Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

Proper way to quote this string in MSQL

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
JoeSnyderJr
Asked:
JoeSnyderJr
  • 2
1 Solution
 
adatheladCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
JoeSnyderJrAuthor Commented:
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
 
JoeSnyderJrAuthor Commented:
Graded you an A even though you ignored my response question. Thanks for the help you provided.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now