SQL Function variable quote passing

I have a function that I need to pass a variable of 'SHM' but I cannot figure out how to get the single quotes to pass in variable. Thanks in advance. I am using SQL Server 2000.

 
Function Query

select * from dbo.sales_union_func  
(
'SHM' -- Needs to pass as 'SHM' not SHM
 )

Open in new window

CREATE function sales_union_func (

    @assoc

	
) returns table AS return


select * from stock where assoc=@assoc

Open in new window

ubsmailAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
are you 200% sure that you need to pass quotes ?

if yes:

select * from dbo.sales_union_func  ( '''SHM'''  )
0
ubsmailAuthor Commented:
I am sure but that didnt work. Any other suggestions?
--this returns results--

select top 1000 * from stock where assoc='SHM'

Open in new window

 
--FUNCTION

CREATE function assoc_test (
    @assoc char
) returns table AS return

select top 1000 * from stock where assoc=@assoc

--QUERY
select * from assoc_test ('''SHM''')

--Does not return results

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
In the question, you do NOT have any data type defined for @assoc and then in the above you have just char. You are likely passing just a single quote for comparison. As angeliii eluded to, you do NOT need to pass the quotes if the parameter is defined with the proper data type to match the data type of the column assoc; therefore, this should work for example:

CREATE function dbo.assoc_test (
    @assoc char(3)
) returns table AS return
select top 1000 * from stock where assoc=@assoc
GO

select * from dbo.assoc_test('SHM');

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree, if you want to pass a string, once you define the variable as string, it's ok to pass it like shown.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.