Identifiers in Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identifiers in Dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-10 : 00:45:16

Travis writes "Here's the situation. I have a sp with a bunch of unioned queries. Works fine. I now want to pass a bit of dynamic sql to a few of the queries. These are passed on a condition from an asp page.

I tried delimiting the variables with double quotes, but get the error "The identifier that starts with 'Select Top 1 'General...' is too long. Maximum length is 128...
I thought I might be able to fix it with SET quoted identifier off, but no.

Here is the SQL that renders this error. @MPT is the SQL passed from the ASP page. It is either
AND sess_user NOT IN (SELECT tLanID FROM dbo.tblMembers)
or nothing
:

CREATE PROCEDURE [dbo].[spStats]
(
@StartDate datetime,
@EndDate datetime,
@MPT varchar(400)
)
AS
SET quoted_identifier OFF
DECLARE @SQL varchar(8000)
SELECT @SQL =
"
SELECT TOP 1 'General Site Details' AS COL0, '' AS COL1, '' AS COL2, '' AS COL3 FROM dbo.LOG_REC

UNION ALL

SELECT 'Total Pages Viewed' AS COL0, '' AS COL1, '' AS COL2, CAST(COUNT(*)AS varchar(10)) AS COL3
FROM dbo.LOG_DATA
WHERE log_datetime >" + @StartDate + " AND log_datetime < " + @EndDate

+ "UNION ALL

SELECT 'Total Sessions' AS COL0, '' AS COL1, '' AS COL2, CAST(COUNT(*) AS varchar(10)) AS COL3
FROM dbo.LOG_REC
WHERE sess_start >" + @StartDate + "AND sess_end <" + @EndDate + @MPT

+ "UNION ALL

SELECT... etc."

EXEC(@SQL)

If I don't delimit the variables, and just put double quotes around the whole unioned SQL statement, the syntax checks fine, but the asp page fails and I get an error that I must declare the variable '@StartDate'

I assume this is because the variables aren't delimited in the SP.

I don't know where to go from here."

Nazim
A custom title

1408 Posts

Posted - 2002-06-10 : 01:56:56

Hi Travis,

Remember whenever you are using quotes to in a string . prefix it with another single quotes. and always avoid using double quotes.

eg:
select @sql='SELECT TOP 1 ''General Site Details'' AS '

hth

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources