Solved

Proper way to quote this string in MSQL

Posted on 2008-10-22
4
605 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 66
Analysis of table use 7 43
How can i get data when i use where clause with group by? 3 22
create insert script based on records in a table 4 15
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now