How to convert my Function to Dynamic SQL

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

Transact-SQL (2000)

How to convert my Function to Dynamic SQL

Author

Topic

nayanancha
Starting Member

27 Posts

Posted - 2011-11-04 : 11:52:31

I have the below function. I want to change this Dynamic SQL. I need to pass the 3 tables coz it wont be the same always, I wud be passing 2 tables along with my VId.

I am new to SQL how to change this function to dyamic SQL

Thanks,

ALTER FUNCTION [dbo].[ConcatenateCompanyCodeByVendor](@VId int)
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @Output VARCHAR(max)

SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), C.CompanyCode)
from Portal.dbo.Company C inner join Portal.dbo.CompanyVendor CV on C.Id = CV.CompanyId
inner join Portal.dbo.FrontendVendor FV on CV.VendorId = FV.Id
where
FV.Id = @VId
RETURN @Output
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 12:54:05

why do you need dynamic sql? if your tables are fixed whats the need of dynamic sql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nayanancha
Starting Member

27 Posts

Posted - 2011-11-04 : 13:59:28

It wont be fixed. Depending upon the mode, my table changes thats why I need to implement Dynamic SQL.

Thanks,

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-04 : 14:55:59

it would be much better to either:

1) Not concatenate the strings in the db at all. If this is a display layer thing then just select them individually and then do some post proccessing of your results
2) encapsulate the logic of what you want to do into another table (or a view), ( probably as easily populated as UNION SELECT a bunch of other queries. You could maybe even materialise this view if you can define some sort of unique constraint on it.

Also -- you *cant* use dynamic sql inside functions. It would break the access contract of the function. Functions in microsoft sql cannot change tables and they must be deterministic -- given the same inputs they will return the same outputs (assuming the underlying data has not changed from call to call). As part of this, dynamic sql is forbidden in functions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2011-11-04 : 15:15:44

"I need to pass the 3 tables coz it wont be the same always"

Why? I expect that is the "nub" of the question as to how to solve this.

Most likely either your database design could be improved, or the situation you find yourself in could perhaps be solved by a different route.

So please explain why you think you need a function that can have a parameter indication which table(s) it should operate on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources