Stored Proc input parameters
-
Here is my proc:
IF EXISTS (SELECT * FROM sys.sysobjects WHERE name = 'Usp_Col' AND xtype = 'P')
BEGIN
DROP PROC Usp_Col
END
GO
CREATE PROC Usp_Col
(@Col1 VARCHAR(10),@Col2 VARCHAR(10))
AS
BEGIN
DECLARE @Collist VARCHAR(100),@Query VARCHAR(100)
SET @Collist = @Col1 + ',' + @Col2
SET @Query = 'SELECT ' + @Collist + ' FROM pubs.dbo.authors'
EXEC (@Query)
END
EXEC Usp_Col au_lname,au_fname
EXEC Usp_Col au_lname,null
EXEC Usp_Col null,au_fname
The 1st Exec statement gives the result set but the 2nd & 3rd gives no result but the following message "Command(s) completed successfully." How to retrieve the records from the proc if I pass null as one of the input parameters?
Any help is greatly appreciated.
Thanks in advance.
-
Your problem is that you're passing nulls in, and trying to add a null to a varchar string. This can result in one of two things: a null, or a blank string.
SET CONCAT_NULL_YIELDS_NULL ONPRINT 'A' + null + '1' -- Prints nothingSET CONCAT_NULL_YIELDS_NULL OFFPRINT 'B' + null + '2'-- Prints 'B2'Now, if you run EXEC(@Query) when @Query is null, you'll get nothing but "Command Executed Successfully":
DECLARE @Query varchar(100)SET @Query = null -- Redundant, because it will already be nullEXEC (@Query)So, the solution is to put SET CONCAT_NULL_YIELDS_NULL OFF at the top of your stored procedure.
-
U need to put condiition to build @Collist string.....
Try replacing <SET @Collist = @Col1 + ',' + @Col2> with .........
---------------------------
if @Col1 is null
SET @Collist = @Col2
else if @Col2 is null
SET @Collist = @Col1
else
SET @Collist = @Col1 + ',' + @Col2
---------------------------
-
sumit.agrawal (7/16/2010)
U need to put condiition to build @Collist string.....Try replacing <SET @Collist = @Col1 + ',' + @Col2> with .........
---------------------------
if @Col1 is null
SET @Collist = @Col2
else if @Col2 is null
SET @Collist = @Col1
else
SET @Collist = @Col1 + ',' + @Col2
---------------------------
That's a good point that I missed. Even if you SET_CONCAT_NULL_YIELDS_NULL off, you'll still have extra commas in your statement, and it will generate a syntax error.
-
Thank You Sumit.
-
Thank You Jim
-
I would do it like this:
@Collist = COALESCE( @Col1 + ',' + @Col2, @Col2, @Col1)
This will set @Collist to the first one of these that comes back without a NULL value. You may have to play with the order to get exactly what you want. With this one you will get @Col1 returned if @Col2 is NULL.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply