Solved

Proper way to quote this string in MSQL

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to check data in sql table 11 47
Query to Add Late Tolerance 10 67
Help with SQL - TOP 10 by date and by group 13 39
Getting max record but maybe not use Group BY 2 27
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

786 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