Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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

0
ubsmail
Asked:
ubsmail
  • 2
1 Solution
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now