Link to home
Create AccountLog in
Avatar of viasyshc
viasyshc

asked on

SQL - Dynamic Columns In Stored Procedure

Hello -

Receivig this 'INT' error in my SP and have been researching it here on EE. I know why it's throwing the error but I cannot figure out how to correct my specific code based on the other answers posted on EE. I'm guessing I have to CAST my int value, see what you think:
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].[pricing_getParts_CP]
@dbname varchar (20)
AS
SELECT CorpWeb.price_parts.partID,partNumber,partNumberDE,partDesc,category,intlistUS,domlistUs,intListEuro FROM CorpWeb.price_parts INNER JOIN price_access ON CorpWeb.price_parts.partID = price_access.partID
 WHERE @dbname  = 1
 AND price_access.partID <> 0
AND price_access.userID = 0
 AND CorpWeb.price_parts.partNumber <> ''
 AND category <> 'Model'
 ORDER BY partnumber,partnumberDE ASC
GO
-----------------------------------------
@dbname is the dynamic column name from my ASP page, in this case the value would be 'vmax'. The price_access.vmax column is an INT column and II'm specificallly looking for the value 1.

Any thoughts/guidance appreciated!

Thanks -
Avatar of chapmandew
chapmandew
Flag of United States of America image

you can't do it that way....something like this instead...

CREATE PROCEDURE [dbo].[pricing_getParts_CP]
@dbname varchar (20)
AS
declare @x varchar(5000)
set @x = '
SELECT CorpWeb.price_parts.partID,partNumber,partNumberDE,partDesc,category,intlistUS,domlistUs,intListEuro FROM CorpWeb.price_parts INNER JOIN price_access ON CorpWeb.price_parts.partID = price_access.partID
 WHERE ' + @dbname  + ' = 1
 AND price_access.partID <> 0
AND price_access.userID = 0
 AND CorpWeb.price_parts.partNumber <> ''''
 AND category <>''Model''
 ORDER BY partnumber,partnumberDE ASC'
exec sp_executesql @x
GO
Avatar of viasyshc
viasyshc

ASKER

Thanks for the fast response!

Tried you code; Syntax was cleared but when page ran I received this error:
------------------------------------------
Microsoft OLE DB Provider for SQL Server error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
---------------------------------------------

Here's the call from the ASP page if it matters/helps:

----------------------------------------------
DIM dbname,strSQL
dbname = request.querystring("dbname")

strSQL = "EXECUTE pricing_getParts_CP '" & dbname & "'"
Set objPagingConn = Server.CreateObject("ADODB.Connection")
objPagingConn.Open dataConn

ETC.......

try to run your stored procedure in query analyzer or management studio and see what error(s) you get...
In Query Analyzer this code:
------
declare @dbname varchar (20)
declare @x varchar(5000)
set @x = '
SELECT CorpWeb.price_parts.partID,partNumber,partNumberDE,partDesc,category,intlistUS,domlistUs,intListEuro FROM CorpWeb.price_parts INNER JOIN price_access ON CorpWeb.price_parts.partID = price_access.partID
WHERE ' + @dbname  + ' = 1
AND price_access.partID <> 0
AND price_access.userID = 0
 AND CorpWeb.price_parts.partNumber <> ''''
 AND category <>''Model''
 ORDER BY partnumber,partnumberDE ASC'
exec sp_executesql @x
GO
----------

Generated this error:

------------------
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
------------------

Line 11 in my viewer is the " exec sp_executesql @x " bit.

Forgive me if I'm missing the obvious; fairly new at this whole process.....

In my original SP above, if I subsituted "WHERE vmax
 =1" instead of "WHERE @dbname = 1" the SP ran perfectly.

Thanks -
post the actual procedure....is @statement a param?
It is posted above exactly as enterd into Query Analyer, with the resulting error. I had removed the first line " CREATE PROCEDURE [dbo].[pricing_getParts_CP] " becuase it was trying to create the SP again (error that it already existed) so I took it out. When it ran it complained that I must declare @dbname, which I did. Running it again resulted in the posted error.

No, @statement is not a paramaeter; not sure where it's getting that from. I assumed it was liooking for a value for @dbname. Tried hard coding "vmax = 1" and receieved same "@statement" error from Query Analyzer.
@statement has to be somewhere in your query analyzer window.....
Don't see it, but here is the copy & paste out of the QA window again:
---------
declare @dbname varchar (20)
declare @x varchar(5000)
set @x = '
SELECT CorpWeb.price_parts.partID,partNumber,partNumberDE,partDesc,category,intlistUS,domlistUs,intListEuro FROM CorpWeb.price_parts INNER JOIN price_access ON CorpWeb.price_parts.partID = price_access.partID
 WHERE ' + @dbname  + ' = 1
 AND price_access.partID <> 0
AND price_access.userID = 0
 AND CorpWeb.price_parts.partNumber <> ''''
 AND category <>''Model''
 ORDER BY partnumber,partnumberDE ASC'
exec sp_executesql @x
GO
----------------------------------------
..and the error:

-------------------
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
----------------------------
Line 11 is the " exec sp_executesql @x "

Is 'sp_executsql' standard syntax for executing sql? I ask because I have not seen it before and it is not defined anywhere in the code.  




Raising points on this; hopefully somebody will point out the error of my ways.....
POST all of your code...@statement HAS to be in the code somewhere...post the entire procedure...we cannot help you if you do not.
My fault...i found it...this fixes it....

declare @dbname varchar (20)
declare @x nvarchar(4000)
set @x = '
SELECT CorpWeb.price_parts.partID,partNumber,partNumberDE,partDesc,category,intlistUS,domlistUs,intListEuro FROM CorpWeb.price_parts INNER JOIN price_access ON CorpWeb.price_parts.partID = price_access.partID
 WHERE ' + @dbname  + ' = 1
 AND price_access.partID <> 0
AND price_access.userID = 0
 AND CorpWeb.price_parts.partNumber <> ''''
 AND category <>''Model''
 ORDER BY partnumber,partnumberDE ASC'
exec sp_executesql @x
GO
Funny thing is, I've your error 100 times before, and for some reason didn't realize what it was...
Stored Procedure entered :
------------------------------
CREATE PROCEDURE [dbo].[pricing_getParts_CP]
declare @dbname varchar (20)
declare @x nvarchar(4000)
set @x = '
SELECT CorpWeb.price_parts.partID,partNumber,partNumberDE,partDesc,category,intlistUS,domlistUs,intListEuro FROM CorpWeb.price_parts INNER JOIN price_access ON CorpWeb.price_parts.partID = price_access.partID
 WHERE ' + @dbname  + ' = 1
 AND price_access.partID <> 0
AND price_access.userID = 0
 AND CorpWeb.price_parts.partNumber <> ''''
 AND category <>''Model''
 ORDER BY partnumber,partnumberDE ASC'
exec sp_executesql @x
GO
-------

Generates this error when clicking "Check Syntax":

----
ERROR 156: Incorrect syntax near the keyword 'declare'
----
Tried removing declare from @dbname, no luck

Tried removing declare from @x, no luck

What did you find that you changed? I don't see an "AS" statement in your last post; tried putting that in with no luck either.

Also, running your latest post in Query Analyzer (without the CREATE... line) reported as running successfuly, but no results were returned (?).  Probably because 'dbname' has no value there.

Ugh....
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Well, getting a "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." error but beleive it is related to either my data connection or something in the ASP page and outside the scope of this SP issue. I beleive the SP is working correctly so I thank you and award the points. Off to fight this other issue......
Thanks!