Call function

19 Jan 2006 2:30 PM

BGO.UGent

If have created this function for SQL Server 2005 Express, and want to
call it from C#.

Function is called in SQL as :
SELECT pkstock, fkStock, dbo.fGetStockPositionByName('0.5') from
tblStock where pkStock = '6'

compleet sql script included.

How must I build this code for passing the variables en retrieving the data.
Is this simular to a Stored Proc?

Show quote

> -- Set active DB
> USE WE08ELO
> go
>
>
> -- Check for function
> if exists (select * from sysobjects where name='fGetStockPositionByName')
>         drop function fGetStockPositionByName
> go
>
> -- Create function
> Create function fGetStockPositionByName (@StockName as varchar(5)) Returns nvarchar(500)
> as
> begin
>
>     -- functie
>     DECLARE @node int
>     DECLARE @parent int
>     DECLARE @tekst nvarchar(5)
>     SET @node = 0
>     SET @parent = 0
>     SET @tekst = ''
>
>     DECLARE @results nvarchar(500)
>     DECLARE @janee tinyint
>     SET @results = ''
>     SET @janee = 0
>
>     -- select node, child, shortname from tblStock where shortname = @StockName
>     select @node=pkStock, @parent=fkStock, @tekst=shortname from tblStock where shortname = @StockName
>     SET @results = @tekst
>
>     WHILE @parent <> 0
>         BEGIN
>             select @node=pkStock, @parent=fkStock, @tekst=shortname from tblStock where pkStock = @parent
>             SET @results = @tekst + '/' + @results
>         END
>
>     return @results
> end
> Go
>
> -- Call of the function
> -- SELECT pkstock, fkStock, dbo.fGetStockPositionByName('0.5') from tblStock where pkStock = '6'

19 Jan 2006 3:42 PM

Paul Clement

On Thu, 19 Jan 2006 15:30:18 +0100, "BGO.UGent" <bgo.UG***@gmail.com> wrote:

¤ If have created this function for SQL Server 2005 Express, and want to
¤ call it from C#.
¤
¤ Function is called in SQL as :
¤ SELECT pkstock, fkStock, dbo.fGetStockPositionByName('0.5') from
¤ tblStock where pkStock = '6'
¤
¤ compleet sql script included.
¤
¤ How must I build this code for passing the variables en retrieving the data.
¤ Is this simular to a Stored Proc?

Yes, but I would recommend wrapping the above SQL in a stored procedure and then calling the sp from
your code:

http://www.codeguru.com/columns/VB/article.php/c8671/

Paul
~~~~
Microsoft MVP (Visual Basic)

19 Jan 2006 4:01 PM

BGO.UGent

Thanks Paul for the link for the .net Code.

I tried to wrap a SP around the function and comes to something like this:
Create Procedure spGetStockPositionByName
    (
        @StockName as varchar(5)
        ,@StockID as int
    )
as

SELECT
    pkstock
    ,fkStock
    ,dbo.fGetStockPositionByName(@StockName)
FROM    tblStock
WHERE    pkStock = @StockID

When i call the function [Select pkstock, fkStock,
dbo.fGetStockPositionByName('0.5') from tblStock where pkStock = '6']
I get the following result
6    3    INW/S12/0.5

when I call the SP [Exec spGetStockPositionByName @StockName =
'0.5',@StockID = '6']
I get the same result
6    3    INW/S12/0.5

So now I translate you VB code to C# and I can start working !!!

Code is added as extra value for people go might need the code.

Bart Goossens

Paul Clement wrote:
Show quote

> On Thu, 19 Jan 2006 15:30:18 +0100, "BGO.UGent" <bgo.UG***@gmail.com> wrote:
>
> ¤ If have created this function for SQL Server 2005 Express, and want to
> ¤ call it from C#.
> ¤
> ¤ Function is called in SQL as :
> ¤ SELECT pkstock, fkStock, dbo.fGetStockPositionByName('0.5') from
> ¤ tblStock where pkStock = '6'
> ¤
> ¤ compleet sql script included.
> ¤
> ¤ How must I build this code for passing the variables en retrieving the data.
> ¤ Is this simular to a Stored Proc?
>
> Yes, but I would recommend wrapping the above SQL in a stored procedure and then calling the sp from
> your code:
>
> http://www.codeguru.com/columns/VB/article.php/c8671/
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)