execute a concatenated query in a procedure

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)

execute a concatenated query in a procedure

Author

Topic

subhankar02dey
Starting Member

4 Posts

Posted - 2010-10-21 : 09:23:49

I have a procedure which is->
============================================================
create procedure procedure1(@city varchar(20),@department varchar(20),@name varchar(20))
as
begin
declare @a varchar(50);
declare @b varchar(50);
declare @c varchar(50);
declare @d varchar(500);
if @city=''
set @a='';
else
set @a='where emp_city='+@city;
if @department=''
set @b='';
else
set @b='and emp_department='+@department;
if @name=''
set @c='';
else
set @c='and emp_name='+@name;
set @d='select * from employees where'+@a+@b+@c;
---???????????????????????????WHAT TO DO TO EXECUTE @d?
return
end;

===========================================================

Now in the procedure, @d holds a select query but it is in varchar format. I cannot execute that as a query in the procedure.
How to execute the query stored in @d?
pls help.

xholax
Starting Member

12 Posts

Posted - 2010-10-21 : 11:04:04

try this

EXEC(@d)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-21 : 11:10:39

Do a print to see the statement!
What I see: the WHERE would be listed twice.
First time in 'select * from employees where' and second time in @a

No, you're never too old to Yak'n'Roll if you're too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-10-21 : 13:17:58

[code]
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE procedure1
(
@city varchar(20)
,@department varchar(20)
,@name varchar(20)
)
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)

SELECT @SQL =
'SELECT * FROM employees WHERE 1=1'
+ CASE
WHEN LEN(COALESCE(@city, '')) > 0
THEN ' AND emp_city = @city'
ELSE ''
END
+ CASE
WHEN LEN(COALESCE(@department, '')) > 0
THEN ' AND emp_department = @department'
ELSE ''
END
+ CASE
WHEN LEN(COALESCE(@name, '')) > 0
THEN ' AND emp_name = @name'
ELSE ''
END

EXEC sp_executesql @SQL
,N'@city varchar(20), @department varchar(20), @name varchar(20)'
,@city, @department, @name
GO
[/code]

subhankar02dey
Starting Member

4 Posts

Posted - 2010-10-21 : 14:03:43

thanks xholax

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources