Parameterized Query / Stored 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)

Parameterized Query / Stored Procedure

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-07-17 : 16:07:25

Based on the follow up comment in Jeff's article

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx
I am trying to create dynamic query based on the parameters. However I'm struck here

. I am sure, missing something simple.


CREATE PROC [dbo].[test]
@Param1 nvarchar(50)--,
AS 
BEGIN
DECLARE @sqlstmt nvarchar(4000)
SELECT @sqlstmt = 
'
SELECT 
	[COL1]
FROM
TBL1
WHERE 
'

IF(@Param1 IS NOT NULL OR LEN(RTRIM(LTRIM(@Param1))) > 0)
	BEGIN
		SELECT @sqlstmt = @sqlstmt + 'COL1 LIKE ''%'+@Param1+'%''' -- This works, I need to substitute this with parameters. 
-- SELECT @sqlstmt = @sqlstmt + 'COL1 LIKE ''%@Param1%''' -- This does not work. 
This does not substitute the parameter value because the entire ''%@Param1%'''  becomes '%@Param1%'
	END
-- PRINT @sqlstmt
EXEC sp_executesql @sqlstmt,N'@Param1 nvarchar(50)',@Param1

PRINT @sqlstmt
END
GO

--test 'test'

Thanks
Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-07-17 : 16:44:46

quote:

SELECT @sqlstmt = @sqlstmt + 'COL1 LIKE ''%'+@Param1+'%''' -- This works, I need to substitute this with parameters.
-- SELECT @sqlstmt = @sqlstmt + 'COL1 LIKE ''%@Param1%''' -- This does not work.
This does not substitute the parameter value because the entire ''%@Param1%''' becomes '%@Param1%'

This works they same like the above one in red, I wonder if this open to Sql Injection. Any thoughts?

SELECT @sqlstmt = @sqlstmt + 'COL1 LIKE ''%''+@Param1+''%'''

Thanks
Karunakaran

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-17 : 16:44:57

try

SELECT @sqlstmt = @sqlstmt + 'COL1 LIKE ''%'' + @Param1 + ''%'''

instead of

SELECT @sqlstmt = @sqlstmt + 'COL1 LIKE ''%'+@Param1+'%'''

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-17 : 16:46:18

11 seconds :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources