January 6, 2009 at 4:21 am
Hi to all,
i am ved, can anyone suggest me how to call user defined function in stored procedure.
Thanks & Regards
vedsharma
January 6, 2009 at 4:29 am
you would call it the same way you would if it was outside of a stored procedure.
What kind of function is it.
If you show us the function we can show you how to call it 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 7, 2009 at 6:53 am
CREATE FUNCTION [dbo].[FormattedSKU]
(
@SKU VARCHAR(20)
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @strSKU VARCHAR(20)
SET @strSKU = @SKU
IF LEN(@SKU) = 12
BEGIN
SET @strSKU = SUBSTRING(@SKU,1,1) + '-'
SET @strSKU = @strSKU + SUBSTRING(@SKU,2,5) + '-'
SET @strSKU = @strSKU + SUBSTRING(@SKU,7,5) + '-'
SET @strSKU = @strSKU + SUBSTRING(@SKU,12,1)
END
IF LEN(@SKU) = 8
BEGIN
SET @strSKU = SUBSTRING(@SKU,1,1) + '-'
SET @strSKU = @strSKU + SUBSTRING(@SKU,2,6) + '-'
SET @strSKU = @strSKU + SUBSTRING(@SKU,8,1) + '-'
END
RETURN @strSKU
END
this function takes a SKU number as a string of 8 or 12 characters and returns a string with the dashes i.e
0-00282-00429-3
to call it:
SELECT UPC, dbo.FormattedSKU(UPC)
FROM ProductTable
you need the dbo. because the default is a system, not a user-defined function
January 7, 2009 at 7:08 am
It very simple to call function with an stored proc. below is an example.
CREATE PROCEDURE [dbo].[uspGetSkuCode]
( @Sku VARCHAR(50) )
AS
BEGIN
SET NOCOUNT ON
SELECT [dbo].[FormattedSKU](@Sku)
END
you will get the value as a resultset. if you want it as a output you can add one out parameter to the SP, Store the value in parameter. & just return it..
Abhijit - http://abhijitmore.wordpress.com
January 7, 2009 at 7:38 am
Also here is another way to do what you doing:
DECLARE @strSKU VARCHAR(20)
SET @strSKU = @SKU
IF LEN(@SKU) = 12
BEGIN
SELECT STUFF(STUFF(STUFF(@strSKU,2,0,'-'),8,0,'-'),14,0,'-')
END
IF LEN(@SKU) = 8
BEGIN
SELECT STUFF(STUFF(@strSKU,2,0,'-'),9,0,'-')+ '-'
END
RETURN @strSKU
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply