Solved

Proper way to quote this string in MSQL

Posted on 2008-10-22
4
619 Views
Last Modified: 2012-08-13
Have attempted two forms of quoted string in declared variable:
SELECT @SchemaID = COALESCE(@SchemaID,'') + '' + sc.name + '' + ','  
This form ignores second tick and creates unquoted string.
SELECT @SchemaID = COALESCE(@SchemaID,'') + '"' + sc.name + '"' + ','  
This form creates quoted string but fails in IN(@SchemaID) in select statement below

Not sure what other options I have for this.

DECLARE @SchemaID VARCHAR(1000) 
DECLARE @SQL VARCHAR(MAX) 
 
-- CREATES COMMA SEPARATED LIST OF ALL USER SCHEMAS
SELECT @SchemaID = COALESCE(@SchemaID,'') + '' + sc.name + '' + ','  
  FROM sys.schemas sc
  WHERE principal_id=1 and sc.name<>'dbo'
  ORDER BY sc.name;
 
--SELECT @SchemaID AS Sites
 
--CREATE EXE SCRIPT TO DROP ALL USER INDEXES
SELECT @SQL=COALESCE(@SQL,'')+CAST('SELECT DROP INDEX ' +sc.name + '.' + so.name + '.' + si.name + '; ' AS VARCHAR(MAX)) 
FROM sysindexes si WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK) ON si.id = so.object_id
INNER JOIN sys.schemas sc WITH (NOLOCK) ON sc.schema_id = so.schema_id
WHERE so.type = 'U' --user tables only
AND si.name NOT LIKE '[_]%'  --only true indexes, not statistics
AND si.name NOT LIKE 'dt%'   --don't remove display control tables
AND status=4098  -- only contraints
-- this works
AND sc.name IN ('F100','F01')
-- this doesn't
AND sc.name IN (@SiteID)
order by sc.name, so.name, si.name
 
SELECT @SQL

Open in new window

0
Comment
Question by:JoeSnyderJr
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
ID: 22781190
Hi,

You can't use IN like this with a variable containing CSV values. You either need to use dynamic sql to concatenate the values of @SchemaId into the query...

e.g.

DECLARE @Values VARCHAR(100)
SET @Values = '1,2,3,4' -- IDs of records to return

SELECT * FROM MyTable WHERE Id IN (@Values) -- will not work
EXECUTE('SELECT * FROM MyTable WHERE Id IN (' + @Values + ')') -- this will work

This would be a little more complicated in your case, but just apply that principle.

OR....you can do this:
DECLARE @Values VARCHAR(100)
SET @Values = '1,2,3,4' -- IDs of records to return
SELECT * FROM MyTable WHERE ',' + @Values + ',' LIKE '%,' + CAST(ID AS VARCHAR) + ',%'

In your example this would be a case of changing:
AND sc.name IN (@SiteID)
to
AND ',' + @SiteID + ',' LIKE '%,' + sc.name + ',%'

(Just make sure @SiteID doesn't contain quoted values)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22781293
You can also use a split function like this one: http:Q_23786715.html.
AND sc.name IN (SELECT SplitValue FROM dbo.split(@SiteID, ','))

Open in new window

0
 

Author Comment

by:JoeSnyderJr
ID: 22805078
Thanks for two alternative solutions. Managed to get non execute version to work but still have question on execute solution.

This statment works
SELECT @SQL=COALESCE(@SQL,'')+CAST('SELECT DROP INDEX ' +sc.name + '.' + so.name + '.' + si.name + '; ' AS VARCHAR(MAX))
FROM sysindexes si WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK) ON si.id = so.object_id
INNER JOIN sys.schemas sc WITH (NOLOCK) ON sc.schema_id = so.schema_id
WHERE so.type = 'U'
AND si.name NOT LIKE '[_]%'
AND si.name NOT LIKE 'dt%'
AND status=4098
AND sc.name IN ('F100','F01')
order by sc.name, so.name, si.name

However if I enclose it in EXECUTE('') and remove "AND sc.name IN ('F100','F01')" it doesn't. It seems to have problem with syntax around periods in line 1. Have tried double quoting literals to fix but no help. Given that this doesn't work I am not able to employ the second technique you recommend.
0
 

Author Closing Comment

by:JoeSnyderJr
ID: 31508955
Graded you an A even though you ignored my response question. Thanks for the help you provided.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question