concatenate where clause from string 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)

concatenate where clause from string problem

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2010-04-28 : 13:05:27

Hi I have a stored proc that receives a string where clause that the application passes to the stored proc as parameter.

I first need to calculate a variable value but am unsure how since I have to concatenate my sql because of a variable where clause. If I didn't have to concatenate it would be easy.

I can't just run the execute first as I have to store the value for use in the 2nd query.

SET @RowCount= ('select count ... from ...' + @WHERECLAUSE)

and then my second query

SET @SQL='
'select *,'+@RowCount+','+@ConstValue-@RowCount+'
from MyTable'
+ @WHERECLAUSE

execute @SQL

The above will fail as the sql string will tried to be assigned to @RowCount instead of the count. Is there a way to assign a variable to an execute results e.g the row count?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-28 : 13:14:30

You can use sp_executeSQL. I think it should give the solution you are looking for..

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

adlo
Posting Yak Master

108 Posts

Posted - 2010-04-28 : 13:21:43

If basically want to assign a variable to the result of the first query but I get invalid syntax so it doesn't allow assign a variable to an execute statement.

set @sqlRowCount='SELECT ....'+@WhereClause
set intRowCount=execute (@sqlRowCount)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-28 : 13:22:44

Try this:

Declare @RowCount int
DECLARE @SQLString NVARCHAR(2000);
DECLARE @ParmDefinition NVARCHAR(500);

SET @SQLString = N'Select @RowCount = count(*) from MyTable'

SET @ParmDefinition = N'@RowCount int output';

EXECUTE sp_executesql @SQLString, @ParmDefinition,
@RowCount output

Print @RowCount

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

adlo
Posting Yak Master

108 Posts

Posted - 2010-04-28 : 13:51:28

Thanks ph bohra that works.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-28 : 23:08:57

You are welcome

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources