Please assist dynamic selection of column not working

Question

Post reply

Please assist dynamic selection of column not working

masangombali2

SSC Enthusiast

Points: 181
More actions
October 25, 2013 at 10:57 pm

#285241

ALTER PROCEDURE [dbo].[OtherCases]
@ctype NVARCHAR(24)
AS
BEGIN
declare @colname varchar(100);
declare @strQry Nvarchar(1000);
SET @colname = (SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%' + @ctype + '%'
AND COLUMN_NAME like '%Page_Count%');
SELECT @strQry = N'SELECT originalName, '+@colname +', contentRepoHandle
FROM dbo.CE_table y, dbo.DSObject_table x
WHERE x.handle_id =y.ce_key AND
contentRepoHandle like % '+@ctype+ '% AND contentRepoHandle like %pdf%';
exec sp_executesql @strQry;
END
I call the procedure
exec Othercases 'domestic'
Error : invalid character next to domestic
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You're missing quotation marks around the second WHERE predicate:

contentRepoHandle like % '+@ctype+ '%

should be

contentRepoHandle like ''%'+@ctype+ '%''

The same applies to %pdf%

-- Gianluca Sartori

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

And you need to parameterize your dynamic sql. What you are doing is directly executing a parameter to your proc. This is the text definition of sql injection.

See if this works for you.

SELECT @strQry = N'SELECT originalName, '+@colname +', contentRepoHandle

FROM dbo.CE_table y, dbo.DSObject_table x

WHERE x.handle_id =y.ce_key AND

contentRepoHandle like %''@ctype''% AND contentRepoHandle like ''%pdf%''';

exec sp_executesql @strQry, N'@ctype nvarchar(24)', @ctype;

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/