Use varible in String - SQL Server Forums

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)

Use varible in String

Author

Topic

naviet
Starting Member

2 Posts

Posted - 2006-01-24 : 23:45:58

Hi everybody. I make a store follow:

/////////////////////////////////////////////////////////////////////
CREATE PROCEDURE spGetFlowRequest
@EmployeeID nvarchar(10)=null,
@FlowID nvarchar(50)=null,
@Status smallint=null
AS

DECLARE @result nvarchar(2000)
DECLARE @sql nvarchar(500)

SET @sql=N'SELECT dbo.FMS_FLOW.FLOW_NAME, dbo.FMS_EMPLOYEE.EMPLOYEE_NAME, dbo.FMS_FLOW_REQUEST.DATE_POST,
dbo.FMS_FLOW_REQUEST.STEP_CURRENT, dbo.FMS_FLOW_REQUEST.STATUS, dbo.FMS_FLOW_REQUEST.REQUEST_ID
FROM dbo.FMS_FLOW_REQUEST LEFT OUTER JOIN
dbo.FMS_FLOW ON dbo.FMS_FLOW_REQUEST.FLOW_ID = dbo.FMS_FLOW.FLOW_ID LEFT OUTER JOIN
dbo.FMS_EMPLOYEE ON dbo.FMS_FLOW_REQUEST.EMPLOYEE_ID = dbo.FMS_EMPLOYEE.EMPLOYEE_ID WHERE 1=1 '

IF(@EmployeeID<>null)
SET @result=@result+@sql+ ' AND dbo.FMS_FLOW_REQUEST.EMPLOYEE_ID= "'@EmployeeID'"'
IF(@FlowID<>null)
SET @result=@result+@sql+ ' AND dbo.FMS_FLOW.FLOW_ID="'@FlowID'"'
IF(@Status<>null)
SET @result=@result+@sql+ ' AND dbo.FMS_FLOW_REQUEST.STATUS="'@Status'"'
GO
///////////////////////////////////////

IT'S ERROR:

Server: Msg 170, Level 15, State 1, Procedure spGetFlowRequest, Line 17
Line 17: Incorrect syntax near '@EmployeeID'.
Server: Msg 170, Level 15, State 1, Procedure spGetFlowRequest, Line 19
Line 19: Incorrect syntax near '@FlowID'.
Server: Msg 170, Level 15, State 1, Procedure spGetFlowRequest, Line 21
Line 21: Incorrect syntax near '@Status'.

I known I used varibles into string is incorrect.Please help me

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-24 : 23:58:03

SET @result=@result+@sql+ ' AND dbo.FMS_FLOW_REQUEST.EMPLOYEE_ID=''' + @EmployeeID + ''''

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-25 : 00:05:35

I think this is what you are looking for
Create PROCEDURE spGetFlowRequest
@EmployeeID nvarchar(10)=null,
@FlowID nvarchar(50)=null,
@Status smallint=null
AS

DECLARE @result nvarchar(2000)
DECLARE @sql nvarchar(500)

SET @sql=N'SELECT dbo.FMS_FLOW.FLOW_NAME, dbo.FMS_EMPLOYEE.EMPLOYEE_NAME, dbo.FMS_FLOW_REQUEST.DATE_POST,
dbo.FMS_FLOW_REQUEST.STEP_CURRENT, dbo.FMS_FLOW_REQUEST.STATUS, dbo.FMS_FLOW_REQUEST.REQUEST_ID
FROM dbo.FMS_FLOW_REQUEST LEFT OUTER JOIN
dbo.FMS_FLOW ON dbo.FMS_FLOW_REQUEST.FLOW_ID = dbo.FMS_FLOW.FLOW_ID LEFT OUTER JOIN
dbo.FMS_EMPLOYEE ON dbo.FMS_FLOW_REQUEST.EMPLOYEE_ID = dbo.FMS_EMPLOYEE.EMPLOYEE_ID WHERE 1=1 '
set @result = ''
IF(@EmployeeID is not null)
SET @result= @result+ ' AND dbo.FMS_FLOW_REQUEST.EMPLOYEE_ID= ''' + @EmployeeID +''''
IF(@FlowID is not null)
SET @result=@result+ ' AND dbo.FMS_FLOW.FLOW_ID='''+ @FlowID +''''
IF(@Status is not null)
SET @result=@result+ ' AND dbo.FMS_FLOW_REQUEST.STATUS=' + cast( @Status as nvarchar)
set @result = @sql+ @result
print @result
go

naviet
Starting Member

2 Posts

Posted - 2006-01-25 : 01:20:01

Thanks SamC and shallu1_gupta very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources