December 19, 2014 at 5:36 am
Hello,
Is there a way to set a default value for a sp parameter using a select statement(see code bellow)
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,
SELECT @top = COUNT(col1) FROM Event
Thanks
December 19, 2014 at 5:50 am
Nope. You can't do that within the definition of a procedure. So, instead, you can use a local variable and set that within the body of the procedure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2014 at 5:53 am
Well I need the @top parameter so when I call the procedure if the @top is not supplied to return all the rows, else return the @top rows so I don't know if a local variable would help.
Thank you
December 19, 2014 at 5:58 am
Something like this?
SET @top = COALESCE(@top,(SELECT COUNT(1) FROM Events))
John
December 19, 2014 at 6:01 am
either remove the paramter, or reassign it to the new value and ignore whatever is passed:
--ignore whatever was passed, and get the desired top
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,@top int
AS
BEGIN
SELECT @top = COUNT(col1) FROM [Event]
END
--OR
GO
--don't bother with a passed paramter, look up the desired value instead
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int
AS
BEGIN
DECLARE @top int;
SELECT @top = COUNT(col1) FROM [Event]
END
Lowell
December 19, 2014 at 6:07 am
Thank you both. With your answers combined I got the desire result.
First in the parm declaration for the sp I initialize the @top with 0 and then in the sp body:
SET @top = COALESCE(NULLIF(@top,0),(SELECT COUNT(1) FROM Event))
Thanks again,
BR,
Andrei
December 19, 2014 at 3:05 pm
Is there another way to do this without using the Select count statement because for a large table and a large amount of data that is retrieve, it will run a bit slow??
Thank you
December 20, 2014 at 3:26 am
You could put some control flow in your procedure, so that if a value for @top is passed, it used, but if it's null, the query is executed without the TOP clause.
John
December 20, 2014 at 4:26 am
Option 1:
IF @Top IS NULL
BEGIN
SELECT @Top = SUM(pa.rows) RowCnt
FROM sys.partitions pa
WHERE pa.OBJECT_ID = OBJECT_ID('MyDB.dbo.Event')
END
Option 2:
SELECT @Top = COALESCE(@Top,2147483647)
The 2nd option is more efficient.
The logic behind it: with your current approach you'll get all rows of the table anyway if @top is not specified. Therefore, a larger value than the actual row count doesn't change the result set. So it is possible to use the max allowed number for an integer data type instead of touching the table.
December 22, 2014 at 6:41 am
I think a solution without hard coding or using a 'select count(*)' statement or a 'if clause' doesn't exist??
I mean if @top parameter is passed return the @top rows if not return all(SELECT col1,col2,col3...FROM tbl) without using one of above solution.
Thanks a lot,
Regards,
Andrei
December 22, 2014 at 6:45 am
John Mitchell-245523 (12/20/2014)
You could put some control flow in your procedure, so that if a value for @top is passed, it used, but if it's null, the query is executed without the TOP clause.
Just bear in mind these kind of procedures tend to not have a stable plan (plans are at the procedure level, not per statement) and hence can have erratic execution times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 7:03 am
Gail
Yes, I did consider that, but then I noted that could equally be the case if a value of 1 and value of 1,000,000 were passed in for the @top parameter.
John
December 22, 2014 at 7:16 am
John Mitchell-245523 (12/22/2014)
Yes, I did consider that, but then I noted that could equally be the case if a value of 1 and value of 1,000,000 were passed in for the @top parameter.
Kinda, but it's more about a branch of a procedure getting compiled with a parameter value that it can never be executed with. Not too much of a concern with TOP, but with filters can be very nasty.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 11:44 am
maieras.andrei (12/22/2014)
I think a solution without hard coding or using a 'select count(*)' statement or a 'if clause' doesn't exist??I mean if @top parameter is passed return the @top rows if not return all(SELECT col1,col2,col3...FROM tbl) without using one of above solution.
Thanks a lot,
Regards,
Andrei
The only option I can think of is the use of a default value:
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int, @top INT = 2147483647
AS
SELECT TOP(@top) col1,col2,col3...FROM tbl
Yes, it's still a hard coded value. But you're trying to use two different return methods based on the existence of a parameter.
To my knowledge it's impossible to differentiate the methods without any code...
But be careful with the above solution: It'll only return all rows if the second parameter is not provided at all. In any other case it'll use the value provided which may lead to an error (e.g. exec psGetInformationByProduct_Andrei 0,NULL)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply