|
hi all,
i want to write a generic Insert & update procedure.It should work with different tables having diffrerent datatypes.suppose let say table1 may have col1 int,col2 varchar... and let say table2 may have col1 varchar,col2 char..like that..and so on..
generally till now wat iam doing is that iam writting different stored procedure for different tables..
Now i want to write One Insert & update procedure in my project...and for all the tables in the project i want to use only that procedure.
Is it Possible? If yes kindly help me ..on resolving this issue..
Thanks & regards
suman
|
|
|
|
|
You can. But it would most likely end up using dynamic SQL and you might as well just fire that against the database instead.
|
|
|
|
|
hi
thanks for u reply..
can u help me in writing the query for that requirement.If u have any thing about the dynamic sql and like generic procedures ..please help me by giving some sample query.
|
|
|
|
|
What's the difference between @a and @@a type parameters
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
You pass in parameters that have @. @@ relates to internal functions and values inside SQL Server.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
@a is a user defined parameter where as @@<param-name> such as @@Identity is a system defined parameter.
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
hi
@ stands for local variable
@@ Stands for System variables
eg:
declare @name as nvarchar
set @name='hello'
print @name
select @@VERSION
Returns the date, version, and processor type for the current installation of Microsoft® SQL Server
|
|
|
|
|
how can i use the for loop in stored procedure
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
Sonia Gupta wrote: how can i use the for loop in stored procedure
You can't. Use a WHILE loop instead.
|
|
|
|
|
ok thanks
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
how can i use the while loop.example have to to use select statement
for loop=1 to select count(*) from table
next
this is merely an example.i lknow u have have told i can't use the for loop.this is just to make u understand.
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
declare @cnt as int
set @cnt = (select count(*) from table)
while .....
Regards
KP
|
|
|
|
|
You may have to use a cursor.
This depends on what you are doing in the loop.
A cursor will let you process each row of a query individually.
If you google "SQL cursor" you will find lots of examples.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
GuyThiebaut wrote: You may have to use a cursor.
Don't encourage people to use a cursor unless there is absolutely no other choice. Cursors are extremely slow in SQL Server and should be avoided unless absolutely necessary.
|
|
|
|
|
Ooh I feel like I have just been told off
What I have read in manuals is that cursors are generally very greedy in terms of resources.
My experience however has shown that they are perfectly fine to use.
Yes I know it is possible to write a query without the use of a cursor (I'll be damned if I know how to do this though).
I think unless the application is for some huge banking corporation or airline then using a cursor should be fine.
What would you suggest instead of cursors?
You always pass failure on the way to success.
|
|
|
|
|
GuyThiebaut wrote: Yes I know it is possible to write a query without the use of a cursor (I'll be damned if I know how to do this though).
You have to write a query to populate a cursor in the first place. Therefore you must know how to write some queries.
GuyThiebaut wrote: I think unless the application is for some huge banking corporation or airline then using a cursor should be fine.
That's your opinion, and I, along with many qualified DBAs that I know, disagree with it.
GuyThiebaut wrote: What would you suggest instead of cursors?
Write set based queries where possible. Never consider a cursor until all other avenues are exhausted.
SQL Server, along with many other RDBMS systems, works best when dealing with sets of data. It does not work well when dealing with individual rows.
SQL is a declarative language - you declare what you want and then leave it up to the database engine to figure out how to get it. When you use cursors you introduce an element of procedural programming in to it. That means you are forcing it to do it HOW you tell it, which is often not the best way.
There are some situations where CURSORs are needed, e.g. recursive data. But SQL Server 2005 and 2008 are eliminating those cases also with additions to SQL.
At the end of the day there is no single answer as to what you should replace a cursor with because there are many situations where they can be used when they shouldn't.
|
|
|
|
|
Okay - I agree with everything you say.
I have only used cursors where there is no other option, in my opinion, (having said that I have got a link somewhere on how to use a query to act as a cursor - it's rather esotric stuff though) so yes as you say I can generally write a query to avoid cursors.
I have tended to use them where I am piping data into a dts package that needs to be called once data has been aggregated for one group.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Every day you post many questions that are easily answered via google. SQL Server also comes with very good documentation. If you don't learn to do basic research, you will never become a half decent developer.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
hello,
I need to store RTF (from a net richtext boxes RTF property) on an SQL table.
The size could be anything up to 16 or 17 Mb.
Whats the best datatype to use and has anyone got a codesnippet that could help.
if its something like binary then is it possible that I can search the field with a standard SQL statement such as in:
Select * where Fld Like "*Stuff to Find*"
Thanks in advance
Martin
life is a bowl of cherries
go on take a byte
|
|
|
|
|
MartyK2007 wrote: Whats the best datatype to use and has anyone got a codesnippet that could help.
If SQL Server 2000 then IMAGE
If SQL Server 2005 then VARBINARY(MAX)
Since RTF is contains text only with some crazy formatting instructions embedded in the text then you can also use TEXT, NTEXT, VARCHAR(MAX) or NVARCHAR(MAX). The latter two in SQL Server 2005.
MartyK2007 wrote: if its something like binary then is it possible that I can search the field with a standard SQL statement such as in:
Select * where Fld Like "*Stuff to Find*"
I would look at Full text indexing for something like that.
|
|
|
|
|
dont I have to specify a MAX value though for
VARBINARY(MAX)
I wouldnt want to restrict the size if possible (other than database size limits of course)
thanks
Martin
life is a bowl of cherries
go on take a byte
|
|
|
|
|
ok stupid question - just googled VARBINARY(MAX)
thanks for that it seems to fit what I neeed
Martin
life is a bowl of cherries
go on take a byte
|
|
|
|
|
Hi,
This is regarding SQL Server 2005 Reporting Services.
I want t create virtual directories ('ReportServer','Reports') on IIS along with the deployment of my web application.How can i do that?.
Is it compolsury to create those two virtual directories via 'Reporting Service Configuration Tool' in advance?. Can't we create them as on own or by wep application setup? and then deploy our Reports?
please help me in this.
Thanks,
Karuna
Karuna
|
|
|
|
|
This question would fit more in ASP.NET forum. But anyway you can create the virtual directory on IIS when deploy your application by adding a custom action dll to the web setup project. The custom action should have the code to configure the directories as an application in IIS (i.e. as a virtual directory)
Below is some VB.NET code that you could have in your custom action project to configure 'Reports' directory as an application in IIS in the Root directory (i.e. Root - Default Web Site):
Dim IIsBOVirDirRootObj As Object = Nothing
Dim IIsWebVDirObj As Object = Nothing
' Create an instance of the virtual directory object
' that represents the virtual directory in the default Web site.
IIsBOVirDirRootObj = GetObject("IIS://localhost/W3SVC/1/Root")
Try
' Use the Windows ADSI container object "Create" method to create a new virtual directory.
IIsWebVDirObj = IIsBOVirDirRootObj.Create("IIsWebVirtualDir", "Reports")
Catch ex As Exception
End Try
You can use the same idea to create the virtual directory in the ReportServer Website
-- modified at 5:24 Tuesday 9th October, 2007
|
|
|
|
|
Hi,
I have a branch table. Besides the branch ID and branch name I have the following address fields:
AddressLine1
AddressLine2
AddressLine3
PostalCode
ProvinceID
These fields don't have to be inserted and can all be null. ProvinceID is a reference to the Province table. I have to join the tables and I need to bring back all the branches, if there is a link to the Province table or not. How will my JOIN section look like, and which JOIN will I need to make use of??
Please can someone advise.
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
