I always like to post answers if I find them first. I
used the SET QUOTED_IDENTIFIER OFF so that I could use the
double quotation marks when building my string. Here is
the result

SET QUOTED_IDENTIFIER OFF

DECLARE @TARGET VARCHAR(50)
DECLARE @NAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(200)

SET @TARGET = 'DataProcessDB'
SET @NAME = 'DataProcessDB_Data'
SELECT @SQLCMD = "USE " + @TARGET + ";SELECT * FROM
sysfiles WHERE name = '" + @NAME + "'"

EXEC(@SQLCMD)


>-----Original Message-----
>Hello everyone,
>
>I was hoping someone could help me with delimiting a
>tsql_string with a where clause.
>Below is an example of what I am trying to do.
>
>DECLARE @TARGET VARCHAR(50)
>DECLARE @NAME VARCHAR(50)
>
>SET @TARGET = 'DataProcessDB'
>SET @NAME = 'DataProcessDB_Data'
>
>EXEC ('USE ' + @TARGET + ';SELECT size,maxsize,growth
FROM
>sysfiles where name = '' + @NAME + ''')
>
>The returned results are empty when I know this to be
>incorrect. Any recommendations.
>
>TIA
>
>.
>

Report this post to a moderator

Dan,

Actually, you just need a couple extra quote marks. The following works with
the pubs sample database:

DECLARE @TARGET VARCHAR(50)
DECLARE @NAME VARCHAR(50)

SET @TARGET = 'Pubs'
SET @NAME = 'Pubs'

EXEC ('USE ' + @TARGET + ';SELECT size,maxsize,growth FROM
sysfiles where name = ''' + @NAME + '''')

You're better off not using the double-quote to terminate a string, as it's
not ANSI standard SQL.

Ron
--
Ron Talmage
SQL Server MVP


"Dan Egan" <DanielJEgan@hotmail.com> wrote in message
news:27d201c26e5e$a578be10$39e
f2ecf@TKMSFTNGXA08...
> I always like to post answers if I find them first. I
> used the SET QUOTED_IDENTIFIER OFF so that I could use the
> double quotation marks when building my string. Here is
> the result
>
> SET QUOTED_IDENTIFIER OFF
>
> DECLARE @TARGET VARCHAR(50)
> DECLARE @NAME VARCHAR(50)
> DECLARE @SQLCMD VARCHAR(200)
>
> SET @TARGET = 'DataProcessDB'
> SET @NAME = 'DataProcessDB_Data'
> SELECT @SQLCMD = "USE " + @TARGET + ";SELECT * FROM
> sysfiles WHERE name = '" + @NAME + "'"
>
> EXEC(@SQLCMD)
>
>
> >-----Original Message-----
> >Hello everyone,
> >
> >I was hoping someone could help me with delimiting a
> >tsql_string with a where clause.
> >Below is an example of what I am trying to do.
> >
> >DECLARE @TARGET VARCHAR(50)
> >DECLARE @NAME VARCHAR(50)
> >
> >SET @TARGET = 'DataProcessDB'
> >SET @NAME = 'DataProcessDB_Data'
> >
> >EXEC ('USE ' + @TARGET + ';SELECT size,maxsize,growth
> FROM
> >sysfiles where name = '' + @NAME + ''')
> >
> >The returned results are empty when I know this to be
> >incorrect. Any recommendations.
> >
> >TIA
> >
> >.
> >

Report this post to a moderator