Concatenating dynamic string in SP

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)

Concatenating dynamic string in SP

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2008-03-12 : 13:07:21

Hey guys, I 'm coding my very first stored procedure as accessed by a
.NET application. My input parameter is a dynamically built string. I need to concatenate to a sql query within the SP. I've tried using '+' as the concat. character but it doesn't work.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[tmptable_query] (@condition_cl varchar(100)) as select * from temp_table + @condition_cl

Help would be appreciated. Thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-12 : 13:26:24

You'll have to provide more info Fralo. Right now your query doen't make sense. Is @condition_cl a term to be searched for? As in
Select * FROM yourTable WHERE someColumn = @condition_cl

Jim

fralo
Posting Yak Master

161 Posts

Posted - 2008-03-12 : 13:29:55

Sorry. The @condition_cl is actually the whole condition itself such as...

"where col = 'Smith' order by name"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-12 : 15:05:00

Yikes!

http://www.sommarskog.se/dynamic_sql.html

Jim

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-03-12 : 15:08:11

fralo,

You're trying to combine dynamic and static SQL together. You can't do that.

Search this site for 'dynamic SQL'. You'll see some info about how to do what you want.

fralo
Posting Yak Master

161 Posts

Posted - 2008-03-12 : 15:10:54

Thanks guys but I got it to work using the following:

CREATE procedure [dbo].[tmptable_query] (@condition_cl varchar(100))
as
BEGIN
DECLARE @sSQL varchar(2000)
SET @sSQL='select * from temp_table '+@condition_cl
EXEC (@sSQL)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources