Solved

Proper way to quote this string in MSQL

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

15 Experts available now in Live!

Get 1:1 Help Now