July 15, 2010 at 11:49 pm
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.
🙂
July 15, 2010 at 11:55 pm
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 ON
PRINT 'A' + null + '1' -- Prints nothing
SET CONCAT_NULL_YIELDS_NULL OFF
PRINT '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 null
EXEC (@Query)
So, the solution is to put SET CONCAT_NULL_YIELDS_NULL OFF at the top of your stored procedure.
July 16, 2010 at 12:12 am
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
---------------------------
July 16, 2010 at 12:18 am
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.
July 16, 2010 at 8:05 am
Thank You Sumit.
July 16, 2010 at 8:06 am
Thank You Jim
July 16, 2010 at 10:31 am
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