Concatinating a dynamic query inside a stored proc

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 2000 Forums

SQL Server Development (2000)

Concatinating a dynamic query inside a stored proc

Author

Topic

Prithvi
Starting Member

1 Post

Posted - 2003-04-22 : 06:37:18

Hi All,
I have a stored proc which accepts the part of the query passed as an input parameter.Problem I find is how do i include with the query that I am using inside the stored proc.My stored proc is as below

CREATE PROCEDURE proc_getclients @AppendQuery varchar(200)
AS

SET NOCOUNT ON

select ClientMaster.clientid "Client Id",firstname "First Name",lastname "Last Name",email "Email",expirydate "Expiry Date",istrial "Is Trial" from
clientmaster <I want to insert the input param here> JOIN clientprofile ON
(ClientMaster.ClientId = ClientProfile.ClientId)
GO

I want the value of @AppendQuery to replace the text within <>

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-22 : 07:57:27

Build an SQL string and execute

CREATE PROCEDURE proc_getclients

@AppendQuery varchar(200)

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR (2000)

SET @SQL =
'select ClientMaster.clientid "Client Id",firstname "First Name",lastname "Last Name",email "Email",expirydate "Expiry Date",istrial "Is Trial" from
clientmaster' + @append + ' JOIN clientprofile ON
(ClientMaster.ClientId = ClientProfile.ClientId)'

EXEC @SQL
GO

You will need to use double apostrophes in place of single apostrophes inside the string.

Sam

-- I try to avoid dynamic SQL whenever possible. It's slow, harder to debug and read.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 09:13:54

Also take a look at sp_executesql you might just want to pass the string to sp_executesql all the time instead.

Edited by - ValterBorges on 04/22/2003 09:17:43

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources