468,765 Members | 1,036 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Calling All Dynamic SQL Gods!

Hello
I'm desperate for help with the following dynamic SQL. It used to work for
ages but suddenly stopped working today! I can't recall changing anything of
importance.. So I say. Anyway, I'm getting this error: "Cannot use empty
object or column names. Use a single space if necessary."
I've identified the location within the script that causes this message it's
this line:
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
3rd line of Set @cmdSQL =.

I've been trying to insert a single space between "" which eliminates half
of the error but I can't figure out what quotes to use around 'MG' and 'MA'.

I'd be grateful if you can have a look at this and let me know how to
correct this problem.
Like I said it used to work and I'm perplexed about this sudden error. Is
there any change that can cause this behaviour?

Many thanks for your efforts!!
Have a nice day!!

Martin

Paging Script:

CREATE PROCEDURE dbo.sp_ListKeyOut(
@page_number INT,
@number_of_records INT,
@cmdWHERE VARCHAR(200),
@cmdORDERBY VARCHAR(200)
) AS
SET NOCOUNT ON

DECLARE
@SizeString VARCHAR(5),
@PrevString VARCHAR(5),
@cmdSQL varchar(2000)

SET @SizeString = CONVERT(VARCHAR, @number_of_records)
SET @PrevString = CONVERT(VARCHAR, @number_of_records * (@page_number - 1))

SET QUOTED_IDENTIFIER OFF

SET @cmdSQL = 'SELECT COALESCE((i2b_vw_contact.Firstname + CHAR(32) +
i2b_vw_contact.Lastname),i2b_vw_company.CompanyNam e) AS CName,
i2b_vw_keytransactionlog.KeyTransactionLogID,
i2b_vw_keytransactionlog.KeyID,
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
i2b_vw_address.Address1 AS PropertyAddress, i2b_vw_contact.MobileNo,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_vw_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_vw_keytransactionlog.Retur nByDate,104) AS
ReturnByDate
FROM i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN'

IF @cmdWHERE IS NULL OR @cmdWHERE = ''
BEGIN

EXEC(
@cmdSQL +
'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID NOT IN
(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
ORDER BY ' + @cmdORDERBY + ')
ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY
)

-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog')

END
ELSE
BEGIN

EXEC(
@cmdSQL +
'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @cmdWHERE + ' AND KeyTransactionLogID NOT IN
(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @cmdWHERE + ' ORDER BY ' + @cmdORDERBY + ')
ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY
)

-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog WHERE ' + @cmdWHERE)

END

SET QUOTED_IDENTIFIER ON

RETURN 0
GO
Jul 20 '05 #1
4 1795
Martin Feuersteiner (th************@hotmail.com) writes:
I'm desperate for help with the following dynamic SQL. It used to work
for ages but suddenly stopped working today! I can't recall changing
anything of importance.. So I say. Anyway, I'm getting this error:
"Cannot use empty object or column names. Use a single space if
necessary."


In T-SQL there are two ways to delimit a string literal: '' and "". ANSI
SQL permits only '', and reserves "" for quoting identifiers with
"funny" characters, so you can have column names like "order date".

Of this reason, SQL Server offers a setting QUOTED_IDENTIFIER that can
be on or off. If ON, you can only use '' to delimit string literal, if
off, you can use both '' and "".

There is functionality (indexed views, indexed computed columns) in SQL
Server that is only available if QUOTED_IDENTIFIER is ON, so stick with this
setting. This is also the default setting with many client libraries.
Unfortunately, Enterprise Manager has the bad habit of setting it off, and
since this setting is saved with the procedure this can cause confusinon at
times.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

Hi Erland

I knew you would go for it :-)
Well, I know that stuff with '' and ", my problem is that I can't
comprehend how many of ' I need to use e.g. on each site of 'MG' to get
it working. It causes a brain overflow!! Can you please provide me with
a hands-on example perhaps using my code?

(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,

Many thanks for your efforts!
Have a nice day!

Martin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
Martin Ashcroft (th************@hotmail.com) writes:
I knew you would go for it :-)
Well, I know that stuff with '' and ", my problem is that I can't
comprehend how many of ' I need to use e.g. on each site of 'MG' to get
it working. It causes a brain overflow!! Can you please provide me with
a hands-on example perhaps using my code?

(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,


Check out http://www.sommarskog.se/dynamic_sql...good_practices
for some tips.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

Thank you Erland!
It helped!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Julia Briggs | last post: by
5 posts views Thread by Francesco Bochicchio | last post: by
50 posts views Thread by Gosi | last post: by
15 posts views Thread by dspfun | last post: by
21 posts views Thread by Cartoper | last post: by
15 posts views Thread by =?Utf-8?B?VG9tIENvcmNvcmFu?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.