Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Please assist dynamic selection of column not working Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 10:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:00 AM
Points: 9, Visits: 25
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.
Post #1508634
Posted Monday, October 28, 2013 4:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 4,654, Visits: 11,115
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1508800
Posted Monday, October 28, 2013 8:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1508903
Posted Tuesday, October 29, 2013 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:00 AM
Points: 9, Visits: 25
Thanks. I will try it.
Post #1509216
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse