Pass quoted as a Parameter in Function

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)

Pass quoted as a Parameter in Function

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-02 : 06:20:29

Hi,
This is first problem.
i have

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.udfIN(@IN CHAR(100))
RETURNS TABLE 
AS
BEGIN
	DECLARE @sql varchar(100)
	SET @sql ='SELECT * FROM YY WHERE TWO IN (' + @IN + ')'
	RETURN EXEC(@sql)
END 
GO

This throws error as
Msg 102, Level 15, State 31, Procedure udfIN, Line 9
Incorrect syntax near 'BEGIN'.

Second Problem How could i pass the Quoted values as a Parameter in a funation? Actually i want to Execute the function in this manner


SET QUOTED_IDENTIFIER OFF
SELECT * FROM dbo.udfIN("'B','C'")

To just pick the only rows form YY table, which mathches the column TWO, using IN clause.

Help me

Kind Regards,
Thanks.
Gurpreet S. Gill

Kristen
Test

22859 Posts

Posted - 2006-12-02 : 06:26:56

Better to avoid trying to use dynamic SQL and use a SPLIT function instead.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-02 : 09:05:56

Even without Dynamic SQL, syntax of the function definition is wrong!

In a inline-table valued function you can't use BEGIN...END block. There should be only single statement i.e. RETURN.

quote:
Second Problem How could i pass the Quoted values as a Parameter in a funation?

In general, you could pass quoted string anywhere is SQL by escaping it with another quote character:

Select * FROM dbo.udfIN('''B'',''C''')

Harsh Athalye
India.
"Nothing is Impossible"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-02 : 13:18:39

Or, you can go simple... Assuming that the parameters are passed in through a VARCHAR variable called @Parameters...

SELECT *
FROM yourtable
WHERE ','+somecolumn+',' LIKE '%,'+@Parameters+',%'

Now, for some reason, people tend to stay away from such simplicity... perhaps it's the bloody TABLE SCAN it causes

... but it does only take 3 seconds on a million row table.

So... with performance in mind, you could do something like this (I have a test table called "BigTest" with a million rows in it). This little dity returns the 5,112 found rows from the million row table in 216 MILLI-SECONDS. Here's the code without a split function...

DECLARE @Parameters VARCHAR(8000)
    SET @Parameters = ',AA,BB,CC,,EE,'

--    SET @Parameters = ','+@Parameters+','

 SELECT * 
   FROM dbo.BigTest bt,
        (--Derived table "d" contains the split parameters
         SELECT SUBSTRING(@Parameters,N+1,CHARINDEX(',', @Parameters, N+1) - N-1) AS Value
           FROM dbo.Tally
          WHERE N < LEN(@Parameters)
            AND SUBSTRING(@Parameters,N,1) = ','
        ) d
  WHERE bt.SomeValue = d.Value

Notice that the parameter was passed in with leading and trailing commas. If your GUI doesn't do that, just uncomment the line in the code above that adds them... Also notice that there's no special handling required to skip unused parameters and there's no requirement to include single quotes. Kinda handy and your GUI guys should love you...

Ok, you probably also noticed the TALLY table thingy... all it is is a table with a single column of numbers and it has a great many uses not the least of which is the elimination of cursors and while loops for such things as "split" functions.... Every database should have one (or make it part of MASTER)... here's how to make one...

--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates when needed
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
        ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

Finally, if you absolutely must have a function do this for you (and you should in this case), the function would look like this (REAL simple with a Tally table)...


 CREATE FUNCTION dbo.udfSplit(@Parameters VARCHAR(7998))
RETURNS @MyReturn TABLE (Value VARCHAR(7998)) AS
  BEGIN
        --===== Conditionally "Groom" @Parameters to contain a leading and trailing comma
             IF LEFT(@Parameters,1) <> ',' SET @Parameters = ','+@Parameters
             IF LEFT(@Parameters,1) <> ',' SET @Parameters = @Parameters+','

        --===== Split the parameters into the return table
         INSERT INTO @MyReturn (Value)
         SELECT SUBSTRING(@Parameters, N+1, CHARINDEX(',', @Parameters, N+1) - N-1) AS Value
           FROM dbo.Tally
          WHERE N < LEN(@Parameters)
            AND SUBSTRING(@Parameters,N,1) = ','
 RETURN 
    END

... and the previous example would now look like this...

DECLARE @Parameters VARCHAR(8000)
    SET @Parameters = ',AA,BB,CC,,EE,'

 SELECT * 
   FROM dbo.BigTest bt,
        dbo.udfSplit(@Parameters) d
  WHERE bt.SomeValue = d.Value

... and it still only takes about 216 MILLI-SECONDS.

Lemme know if this helps, eh?
--Jeff Moden

p.s. Special thanks to Adam Mechanic for turning me on to Tally tables several years back.

Kristen
Test

22859 Posts

Posted - 2006-12-03 : 02:47:48

"but it does only take 3 seconds [to do a scan] on a million row table"

Not here it doesn't - with, say, 200 concurrently connected users hammering the table in question

Well, it probably does once they've all been deadlocked-out!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources