T-SQL Quiz of the Day -- Multiple Choice
-
I create a stored proc (Northwind):
CREATE PROCEDURE spTest
@EmployeeID INT,
@City VARCHAR(15) = 'London'
AS
SELECT EmployeeID, LastName, FirstName, City
FROM Employees
WHERE EmployeeID = @EmployeeID OR City = @City
ORDER BY City
GO
And I run this command:
EXEC Northwind.dbo.spTest;1 1 -- 'Tacoma'
Do I get back:
(a) Syntax error
(b) Rows with EmployeeID = 1 --or-- City = 'Tacoma'
(c) Rows with EmployeeID = 1 --or-- City = 'London'
(d) Something else
-
c ?
* Noel -
Yes.
At least answer (c) appears to be what I get when I run the command on my SQL 2000 box here.
Still not exactly sure why the ";" doesn't cause trouble -- it appears to "hide" the first "1", so it doesn't get passed to the SQL preprocessor, and there is no syntax error.
- john
-
It doesn't hide it, it ends the statement. ; is a valid character in SQL to end a line of code. (That is one of the tricks involved in using Injection SQL for Dynamic SQL).
I wasn't born stupid - I had to study.
-
Disagree. Although in general you're right about the ";" terminating the command, here if it ended the statement, then there wouldn't be any parameters passed to the proc! Note that the first parameter MUST be specified--it is not a default.
-
I proved myself wrong:
CREATE TABLE Employees( EmployeeID integer,
City varchar(15))
INSERT INTO Employees VALUES( 1, 'Tacoma')
INSERT INTO Employees VALUES( 2, 'London')
INSERT INTO Employees VALUES( 3, 'Pittsburg')
INSERT INTO Employees VALUES( 4, 'Sydney')
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'spTest')
AND OBJECTPROPERTY(id, N'IsProcedure')=1)
DROP PROC spTest
GO
CREATE PROCEDURE spTest
@EmployeeID integer,
@City varchar(15) = 'London'
AS
SELECT @EmployeeID AS '@EmployeeID', @City AS '@City'
SELECT EmployeeID, City
FROM Employees
WHERE EmployeeID = @EmployeeID OR City = @City
ORDER BY City
GO
EXECUTE spTest;1 1 -- 'Tacoma'
DROP TABLE Employees
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'spTest')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROC spTest
GO
I am not sure how the first 1 (one) is passed into the SP.
I wasn't born stupid - I had to study.
-
Yes. Nice work, and thanks!
-
I still don't understand how the first 1 (one) and only that parameter is being passed into the SP. Any help?
I wasn't born stupid - I had to study.
-
I tried
EXEC Northwind.dbo.spTest;2 1 -- 'Tacoma'
It says
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'Northwind.dbo.spTest'.
Regards,
gova -
Try this and look at SP after : )
CREATE PROCEDURE spTest;4
@EmployeeID INT,
@City VARCHAR(15) = 'London'
AS
SELECT EmployeeID, LastName, FirstName, City
FROM Employees
WHERE EmployeeID = @EmployeeID OR City = @City
ORDER BY City
GO
CREATE PROCEDURE spTest;5
@EmployeeID INT,
@City VARCHAR(15) = 'London'
AS
SELECT EmployeeID, LastName, FirstName, City
FROM Employees
WHERE EmployeeID = @EmployeeID OR City = @City
ORDER BY City
GO
CREATE PROCEDURE spTest;6
@EmployeeID INT,
@City VARCHAR(15) = 'London'
AS
SELECT EmployeeID, LastName, FirstName, City
FROM Employees
WHERE EmployeeID = @EmployeeID OR City = @City
ORDER BY City
GO
is this documented somewhere
I figured this when I called with ;0 like he expects more versions of the sp ...
Kindest Regards,Vasc
-
Yes the ; in that case is the VERSION of the stored procedure.
This is an old ODBC standard and I don't recommend it to use because tools like QA or EM hide them in the UI.
if you look at apps like MS access and others that use ODBC you will notice the suffix ;1
hth
* Noel -
Its a Group Number. I did not know SQL Server could do that! Thanks Vasc
Did not realize you had already posted Noeld. Sorry. So the ;1 points to the Group one instance of that SP and since you created that SP, it has already been assigned a Group one instance. Just by chance, the one was used, so it worked.... Wild.
I wasn't born stupid - I had to study.
-
This is cool is there any use for this
SELECT * FROM sysobjects WHERE name like '%spTest%'
Gives one name
SELECT text FROM syscomments WHERE id = (
SELECT id FROM sysobjects WHERE name like '%spTest%'
) and encrypted = 0
ORDER BY number, colid
Gives all texts
DROP PROC spTest
Drops all
Regards,
gova -
It has always been docummented
From BOL:
Syntax
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
Useful for
;
number
Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.
* Noel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply