Function emulation in SQL 7 - 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

Transact-SQL (2000)

Function emulation in SQL 7

Author

Topic

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-03 : 07:41:23

Hi folks,

I know that SQL Server does not have user defined functions but does anybody know a way of simulating them with sp's? I've written an sp that does what I want but I'd like to call it within a select statement if poss.

Can this be done?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-09-03 : 08:46:07

Alas!

in this case "simulating" is equal to "having a real UDF"

and that is impossible.

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-03 : 09:45:32

Noooo! Hmmmmm, so you're telling me that there is no way of doing this?

Hmmm, here is what my sp does:

CREATE PROCEDURE dbo.up_GetPremisesTypeString

(@AddressID int)

AS

DECLARE @PremisesType Varchar(25)
DECLARE @OutputString Varchar(255)

DECLARE curPremises_Type CURSOR FOR

SELECT [Premises Type]

FROM dbo.vwPremisesType

WHERE lngRecordTypeID = @AddressID

SET @OutputString = ''

OPEN curPremises_Type
FETCH NEXT FROM curPremises_Type INTO @PremisesType

WHILE @@FETCH_STATUS = 0

BEGIN
IF @OutputString = ''
BEGIN
SET @OutputString = @PremisesType
END
ELSE
BEGIN
SET @OutputString = @OutputString + ' and ' + @PremisesType
END

FETCH NEXT FROM curPremises_Type INTO @PremisesType
END

CLOSE curPremises_Type
DEALLOCATE curPremises_Type

--Output
Print @OutputString
GO

Literally, I just want to have a delimited list of the type values for a particular address. Given that I don't know what those values are going to be, I need a way of iterating through them that is accessible from within a select statement.

There is a way, surely?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-03 : 10:29:47

Your best bet might be to create a table, like this:

AddressID (PK)
PremisesTypeString (varchar(7000))

and keep that table populated via a trigger whenever your curPremises_Type table changes. Then you can just join to this table whenever you need to return the string of values. This will be much more efficient than your cursor solution as well, by the way. The only overhead is rebuilding this table whenever the curPremises_Type table changes.

- Jeff

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-03 : 10:38:24

I GOT IT!

[url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]

Superb solution!

-- A river flows round it's obstacles.

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-03 : 10:57:18

Okay, I'm very sorry guys but I still have a problem here....I now have:

DECLARE @PremisesTypes varchar(100)

SELECT @PremisesTypes = COALESCE('(' + @PremisesTypes + ' and (', '') +
[Premises Type] + ')'

FROM dbo.vwPremisesType

WHERE lngRecordTypeID = 5676

SELECT @PremisesTypes

...But I am still a bit lost as to how I can get this into a Select query statement.

Sorry to be so dim.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 11:02:13

to put variables into a query you need to do use dynamic sql. or parse them into a tally table and join to that.

Go with the flow & have fun! Else fight the flow :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-03 : 12:41:06

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([Premises Type] varchar(10))
GO

INSERT INTO myTable99([Premises Type])
SELECT 'History' UNION ALL
SELECT 'Sports' UNION ALL
SELECT 'Comedy' UNION ALL
SELECT 'Drama'
GO

DECLARE @PremisesTypes varchar(8000)
SELECT @PremisesTypes = COALESCE(@PremisesTypes + ' and (', '(') + [Premises Type] + ')'
FROM myTable99

SELECT @PremisesTypes
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-06 : 04:03:17

Thanks for all you suggestions folks, will let you know how I get on today.

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-06 : 05:39:52

Okay, this is depressing me now,

What I really need is a way of getting the premises type for each record so even though I'd have the data in the in the table, I'd still have the difficulty of doing the COALESCE for each record.

Do you think it's unavoidable that I would have to use a cursor?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-06 : 06:03:10

well you could put your csv strings into a #temp table along with lngRecordTypeID
then join that table to the parent table.

i guess u can put all that in a sproc.

Go with the flow & have fun! Else fight the flow