Stored Procedure and Coalesce problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure and Coalesce problem

Author

Topic

mj3729
Starting Member

4 Posts

Posted - 2010-02-22 : 14:58:27

I am having some serious problems with a stored procedure that isn't returning anything. Can someone look at my SQL and let me know what am I doing wrong please?

quote:

ALTER PROCEDURE [dbo].[uspGetSource]
(
@FName varchar(510)=''
)
AS
DECLARE
@Contains varchar(510),
@OneLine varchar(510),
@SQL nvarchar(max)

--Initialize Variables
SET @OneLine = ''
SET @Contains = ' '

IF @Fname != ''
SET @Contains = @Contains + 'CONTAINS (FullName, ''' + har(34) + '' +@Fname+ char(34) + ''')'

SET @SQL = N'SELECT @OneLine = @OneLine + coalesce(Gender + '', '', '''') FROM Person WHERE '+ @Contains + ' GROUP BY Gender'
EXEC sp_ExecuteSQL
@SQL,
N'@OneLine varchar(510)',
@OneLine ='';

SELECT @OneLine

When I execute that stored procedure in Microsoft SQL Server Management Studio Express, I can't get an empty table with a column header: (No column name) and another table below with a 0 having a column header: Return Value

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-22 : 15:40:03

It looks like there are a lot of issues there. Try pulling out the SQL and Selecting or Prining bits of it to see what is getting built.

EDIT.. Actually it looks semi-ok.. I cut-n-pasted poorly. :)

I think the issue is around the CONTAINS. If @FName is blank '' or NULL then you loose the WHERE clause.. So there is a syntax error. Here is what I get when I do not specify an @Fname:

SELECT @OneLine = @OneLine + coalesce(Gender + ', ', '') FROM Person WHERE   GROUP BY Gender

mj3729
Starting Member

4 Posts

Posted - 2010-02-22 : 15:47:45

I tried the same SQL statement before and it works well

quote:

SELECT @OneLine = @OneLine + coalesce(Gender + '', '', '''') FROM Person WHERE '+ @Contains + ' GROUP BY Gender

I would replace the @Contains variable with a normal contains statement... and it would work. What issues are you noticing that I may be overlooking? It is as if ... the @OneLine isn't getting any value to it.

mj3729
Starting Member

4 Posts

Posted - 2010-02-22 : 15:51:48

To give some insight into what I am striving to achieve see URL below

http://www.sqlprof.com/blogs/sqldev/archive/2008/03/31/how-to-list-multiple-rows-of-data-on-same-line.aspx

But I am not using a function but a stored procedure.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 15:59:45

There are other (better I think) ways to concatenate records without a UDF..
Check this thread...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-22 : 17:05:43

quote:
Originally posted by mj3729

I tried the same SQL statement before and it works well

quote:

SELECT @OneLine = @OneLine + coalesce(Gender + '', '', '''') FROM Person WHERE '+ @Contains + ' GROUP BY Gender

I would replace the @Contains variable with a normal contains statement... and it would work. What issues are you noticing that I may be overlooking? It is as if ... the @OneLine isn't getting any value to it.

I'm not sure what you setting the @Contains variable to. But, the code you provided in your first post allows for @Fname to be blank (default) and possibly NULL. As I mentioned before if @Fname is blank or null you get a syntax error (bolded below) because the @Contains variable will be blank.

SELECT @OneLine = @OneLine + coalesce(Gender + ', ', '') FROM Person WHERE GROUP BY Gender

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-22 : 20:24:33

I didn't think that the sp_executesql passed back any values and that the scope of the variables used (@OneLine) was limited.
[CODE]DECLARE
@OneLine varchar(510) = 'Hello, World!',
@SQL nvarchar(max)

--Initialize Variables

SET @SQL = N'SELECT @OneLine = ''Goodbye'''

EXEC sp_ExecuteSQL
@SQL,
N'@OneLine varchar(510)',
@OneLine ='';

SELECT @OneLine[/CODE]Returns 'Hello, World!'

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

mj3729
Starting Member

4 Posts

Posted - 2010-02-23 : 09:04:34

Thanks very much guys especially Bustaz Kool. Yall helped in directing in the right direction. Thanks very much too Keith!!! Your patience is much appreciated

quote:
Originally posted by Bustaz Kool

I didn't think that the sp_executesql passed back any values and that the scope of the variables used (@OneLine) was limited.
[CODE]DECLARE
@OneLine varchar(510) = 'Hello, World!',
@SQL nvarchar(max)

--Initialize Variables

SET @SQL = N'SELECT @OneLine = ''Goodbye'''

EXEC sp_ExecuteSQL
@SQL,
N'@OneLine varchar(510)',
@OneLine ='';

SELECT @OneLine[/CODE]Returns 'Hello, World!'

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

There was a slight error though in your code as well. Here is what worked:

quote:

DECLARE
@OneLine varchar(510),
@SQL nvarchar(max)

--Initialize Variables
SET @OneLine = 'Hello World'
SET @SQL = N'SELECT @OneLine = ''Goodbye'''

EXEC sp_ExecuteSQL
@SQL,
N'@OneLine varchar(510) OUTPUT',
@OneLine OUTPUT ;

SELECT @OneLine -- Displays Goodbye

So after going over my code again...I needed to add the OUTPUT keyword after the parameter in the sp_executeSQL stored procedure.

quote:

ALTER PROCEDURE [dbo].[uspGetSource]
(
@FName varchar(510)=''
)
AS
DECLARE
@Contains varchar(510),
@OneLine varchar(510),
@SQL nvarchar(max)

--Initialize Variables
SET @OneLine = ''
SET @Contains = ' '

IF @Fname != ''
SET @Contains = @Contains + 'CONTAINS (FullName, ''' + char(34) + '' +@Fname+ char(34) + ''')'

SET @SQL = N'SELECT @OneLine = @OneLine + coalesce(Gender + '', '', '''') FROM Person WHERE '+ @Contains + ' GROUP BY Gender'
EXEC sp_ExecuteSQL
@SQL,
N'@OneLine varchar(510) OUTPUT',
@OneLine OUTPUT;

SELECT @OneLine

Thanks again guys!!!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-23 : 11:32:59

And thank-you. You showed me an aspect of the sp_ExecuteSql that I wasn't aware of.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources