|
If your intention is to create dynamic SQL strings in a general procedure and execute them, I would not recommend it. First, creating dynamic strings in a .NET enviornment is a bad thing unless you use the stringbuilder class. Second, by creating strings and having the SQL engine execute them will force the SQL engine to parse and develop an execution plan for each statement, this could lead to high CPU usage on your DB server depending on how many SQL statements are being processed per second. You should try to use parameterized queries or stored procedures when dealing with a DB engine, then each time a similar query is requested the statement is already parsed and an execution plan has already been choosen, it will just substitue the parameters in and return your rows. In general, dynamic SQL bad. This is true for almost all DB engines not just MS-SQL server.
Just something to think about ... 
|
|
|
|
|
If you're using sql2k8 you can use Table Value Parameters.
If you're using sql2k5 you can simulate this by passing a list to a stored procedure using XML parameters.
Ryan
|
|
|
|
|
monika_vasvani wrote: I have tried to do one general procedure for insert,update,delete.
Bad practice. The first time the stored proc runs it gets optimised - say it happens to be an insert. The next time it runs it will use the same query plan (as it will be cached), but you may be doing an update, so the optimal query plan would be totally different. This can lead to performance issues. If you want a single proc to call for all the actions have one, but have it call a different proc for insert, another proc for update eetc.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hello everyone,
I create the following table,
create table ABC (a varchar unique);
then create the following store procedure,
CREATE PROCEDURE [dbo].[prc_AddABC]
AS
BEGIN
INSERT INTO [dbo].[ABC]
values ('a');
SELECT @@ERROR AS ABCERROR;
END -- end of store procedure
After execute the procedure twice, which will cause insert duplicate value 'a' on unique column a error.
What I got from Management Studio is,
ABCERROR 2627
return value -4
My question is, why @@error is not the same as return value? what does the message and return value mean? Where could I find
related documents to read their meanings?
thanks in advance,
George
|
|
|
|
|
@@error gives you the error number as SQL Server generates it. You can use it to identify the problem that occured in the database. Described: http://msdn.microsoft.com/en-us/library/ms188790(SQL.90).aspx[^]
George_George wrote: return value -4
Depends where you got this from. If you got this in an exception at client side, it most likely describes the error that the library sees (OleDbCommand, SqlCommand etc.) If thats the case, you can find the SQL Server error number inside the exception.
|
|
|
|
|
Thanks Mika,
I got this error from Management Studio when execute the store procedure I posted in my post. My confusion is, @@ERROR is enough for us to track the exact error, why there is an addtional return value? And who returns -4 -- I did not write return -4 in my store procedure code posted.
regards,
George
|
|
|
|
|
George_George wrote: @@ERROR is enough for us to track the exact error
That's true.
George_George wrote: who returns -4 --
This is something I don't understand. If I create your procedure, run it twice, I get the following result:
Msg 2627, Level 14, State 1, Procedure prc_AddABC, Line 3
Violation of UNIQUE KEY constraint 'UQ__ABC__3BD0198F4316F928'.
Cannot insert duplicate key in object 'dbo.ABC'.
The statement has been terminated.
(1 row(s) affected)
And that's expected. However, no -4 anywhere. Can you post the whole result of your test.
|
|
|
|
|
Hi Mika!
-4 is found from the results window and what you posted is found from message window. I have a screen snapshots, but how could I post here?
BTW: in the message window, I got the same result. But my confusion is the information in the results window -- there are two tabs, one for results window and one for message window.
Could you see the same content in result window?
regards,
George
|
|
|
|
|
Yes that info was from messages window. My result window has this (on the grid, right click / select all / copy with headers):
ABCERROR
2627
So it prints out @@ERROR which is exactly what your procedure does. No -4.
Are you sure the procedure is like in your post? No modifications done? Use the scripting ability and script it to for example query window to see if you do something after the SELECT @@ERROR AS ABCERROR;
About the question of scripting several procedures at the same tame, use the wizard for the database. Select db, right click then Tasks / Generate Scripts... From there on it's simple. Select what you want.
|
|
|
|
|
Hi Mika,
I checked again the code I posted is the same as what I executed. I have some screen snapshots, how could I post here? If we can not post screen snapshot, do you have any email?
regards,
George
|
|
|
|
|
If you're getting -4 on the result tab and the column header is ABCERROR, I have no idea what could be the cause. Since you're selecting @@error it should return the same as the messages tab.
|
|
|
|
|
Thanks Mika!
In the Results Tab, there are two tables,
- One is ABCERROR and content is 2627;
- The other is Return Value and content is -4.
Could you reproduce my issue?
regards,
George
|
|
|
|
|
George_George wrote: Could you reproduce my issue?
Nobody will reproduce this from what you've described. You must have something else in your SP, or the window where you ran it.
Try scripting the stored procedure in management studio and pasting that here.
|
|
|
|
|
Thanks J4amieC,
I have tried that I could reproduce it again. Here is the scripting result from Management Studio. Any ideas?
BTW: -4 is in Results Window, not Message Window.
I could also post screen snapshots here, but how (I did not find upload attachment function here)?
USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[prc_AddABC]
AS
BEGIN
INSERT INTO [dbo].[ABC]
values ('a');
SELECT @@ERROR AS ABCERROR;
END -- end of store procedure
regards,
George
|
|
|
|
|
Hello everyone,
Any ideas to find where is the physical location of store procedures showed under Programmability --> Store Procedures of Management Studio?
(sorry for my naive question again) 
1.
I have a source control system and I want to check-in the store procedure code (I think they should be in the form of .sql) so that all developers could work on them in a synchronous way?
2.
And I also want to merge the store procedure code outside Management Studio using other diff tools and let Management Studio reflect the changes -- e.g. if I merge/change the code from outside, when I press refresh button in Management Studio, I can see the new merged code of store procedures.
thanks in advance,
George
|
|
|
|
|
Um, what you see in management studio is the list of stored procedures available in the database - there's no real concept of a 'physical location'.
I think what you're probably looking for the the sql scripts used to create these procedures - that depends on how you created them in the first place...
|
|
|
|
|
Thanks Paddy,
"that depends on how you created them in the first place" -- I created them using Management Studio. Any solutions or ideas to solve my problems in my original post (to integrate with source control system)?
regards,
George
|
|
|
|
|
There may be a faster way to do this, but as per the post below, you can use management studio to script a creation script for your stored procedure.
We have a .sql file for each stored procedure that drops it if it exists and then creates it - we use these when deploying/updating our application. I imagine that this is what you need.
I don't know if there is a tool out there that will reverse engineer your DB and create these scripts for you (I'm sure there probably is)... Somebody else clever here might suggest one that they've used.
|
|
|
|
|
Thanks Paddy!
I am intersted in using the scripting feature as you mentioned to export code into sql text files and check into source control system. Could you let me know which menu do you mean please (some more steps)? I never did this before.
regards,
George
|
|
|
|
|
Right click on object in the object browser within management studio (can be SP, function, table), Script As, Create To, New file.
|
|
|
|
|
Thanks Paddy!
1.
I find I could only export one store procedure at one time? No quick export command from menu to export all store procedures from a database?
2.
I think half of my question is solved, i.e. how to get the text sql form of store procedure. My second half question is, how to sync code with source control system. I think the solution should be merging the code manually and paste the code into Management Studio, then execute alter store procedure to take effect the new changes? Is that working? Or you have better ideas?
regards,
George
|
|
|
|
|
re point 2:
Depends on your circumstances - if it's a standalone system and you only have one instance of it, then that sounds fine.
If you need to install this in a number of places, having a little installer application that runs through and executes each of the SQL files is probably a good idea - means you can always be sure that the latest of everything is on the machine.
Also, if you have the definitive version in source control, you can check that nobody's been f*cking around with it.
|
|
|
|
|
Thanks Paddy,
1.
How about your answer to my question #1?
2.
I am interested in the installer solution. But how could I invoke SQL statements (e.g. the statements used to create table/store procedures) from an installer application? My confusion is the instlaler application should only be able to call binaries, but how to call sql statements (text, not binary)?
regards,
George
|
|
|
|
|
George_George wrote: Any ideas to find where is the physical location of store procedures
All information concerning a stored procedure is located in system tables. These tables reside in the primary file group of the database. However, the physical location isn't normally relevant for these tables (unless you're interested in complex backup/restore scenarios etc.).
One place you can look at is sys.sql_modules. From that view you can see for example your current procedures and their implementation.
George_George wrote: I have a source control system and I want to check-in the store procedure code
You can use Management Studio's ability to script a procedure. Using it, you'll get an initial version for your source control. After that, modify the script as you like and check-out/check-in when you make modifications. I'm not aware that Management Studio supports any source control system so you may have to do this using file system operations (meaning you checkout to a file and then open it in Management Studio etc)
George_George wrote: And I also want to merge the store procedure code outside Management Studio using other diff tools and let Management Studio reflect the changes
As previously use whetever tools you need and open the result into Management Studio.
George_George wrote: merge/change the code from outside, when I press refresh button in Management Studio, I can see the new merged code of store procedures
You first have to execute the script in order to get the procedure to the database. After that, you'll see the change.
|
|
|
|
|
Thanks Mika!
So many comprehensive comments.
Forgive me that I am a newbie and after reading and following your comments I have some further questions.
1.
"One place you can look at is sys.sql_modules." -- from Management Studio under System Database, I only see 4 database -- master, model, msdb and tempdb. Where could I find the sys.sql_modules as you mentioned?
2.
"You can use Management Studio's ability to script a procedure." -- good point to start! Could you let me know which menu do you mean please (some more steps)? I never did this before.
3.
"As previously use whetever tools you need and open the result into Management Studio."
"You first have to execute the script in order to get the procedure to the database. After that, you'll see the change."
Just confirm your points. My understanding is, I merge the code manually and past the code into Management Studio, then execute alter store procedure to take effect the new changes? Is that what you mean?
regards,
George
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
