Functions Help - 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 2005 Forums

Transact-SQL (2005)

Functions Help

Author

Topic

readie76
Starting Member

2 Posts

Posted - 2008-06-03 : 07:45:33

Hi,

I have created a function that returns a comma seperated list of product id's from a table. I need to call this function from a stored procedure to help filter my product results, something like the following:

SET @SQL = 'SELECT dbo.Products.ProductID FROM dbo.Products WHERE dbo.Products.ProductID IN (' + dbo.GetModels('dbo.Products.ProductID', '') + '))'

The problem I am having when executing the above is:

"Conversion failed when converting the varchar value 'dbo.Products.ProductID' to data type int."

Can anyone shed some light on how I can call the function, feeding through the product ID from the row of the select statement I am trying to execute (if this makes sense).

Any help would be great.

Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 07:57:54

I think the function is expecting an integer while while you're trying to pass the string 'dbo.Products.ProductID' to it. thats why its causing the error.

readie76
Starting Member

2 Posts

Posted - 2008-06-03 : 08:18:34

Yes the function is expecting an integer, but how do I feed in the product ID as an integer?

I have tried several things, firstly by removing the quotation marks to get:

SET @SQL = 'SELECT dbo.Products.ProductID FROM dbo.Products WHERE dbo.Products.ProductID IN (' + dbo.GetModels(dbo.Products.ProductID, '') + '))'

Gave me an error and i could not save the stored procedure.

I then tried romoving the quotation marks altogether to leave:

SET @SQL = 'SELECT dbo.Products.ProductID FROM dbo.Products WHERE dbo.Products.ProductID IN (dbo.GetModels(dbo.Products.ProductID, ''''))'

Although the procedure saved, when trying to execute the procedure I got the following error:

"Conversion failed when converting the varchar value '1, 2, 4, 5, 3' to data type int."

Am pretty well stuck with this, so any help you could give would be grand.

Cheers

Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:39:28

No need for dynamic SQL

SELECT		dbo.Products.ProductID
FROM		dbo.Products
CROSS APPLY	dbo.GetModels(dbo.Products.ProductID) AS g
WHERE		',' + REPLACE(g.Col, ' ', '') + ',' LIKE '%,' + CAST(dbo.Products.ProductID AS VARCHAR(11)) + ',%'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:41:17

This has to be a stupid idea?
Why don't you just INNER JOIN the tables like you do in the function?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources