SQL STORED PROCEDURES

[BFT1303]

I am trying to send a variable to a stored procedure from a C# program. Is this possible and what would be the easiest solution?

THanks,
Brad

 

[AlexCuse]

create proc IHaveParam (@param varchar(1000))

as

begin

select *
from leTable 
where leColumn = @param

end

Then, from your C# program you can call it using a query like this:

execute IHaveParam 'parameter value in single quotes'

Hope this helps,

ALex



Ignorance of certain subjects is a great part of wisdom

 

[BFT1303]

CREATE PROCEDURE get_Parts
AS
DECLARE
@partNum varchar(40)
BEGIN

SET @partNum = 'myPart'

SELECT DISTINCT BIN_ID,SUM(BIN_QTY) AS BIN_QTY FROM plm_brad.LEI_WARE WHERE PART_ID = @partNum GROUP BY BIN_ID
END


Sorry, I didnt explain my problem very well. I would like to pull in the value of 'myPart' into this SQL stored procedure from my C# program.

 

[philhege]

You would accomplish this through a Data Access Logic Component, or DALC. The component would consist of methods to connect to the SQL instance, create a command object, append the SP parameter collection to the object, call the SP, and pass the result back to the business logic object.

Google "ADO.NET examples" for, well, examples of how to do this. I'd submit, but my stuff's all in VB.

Good luck.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[gmmastros]

You want to return that value of @partNum for each record returned by the query? If so, then...

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] get_Parts    
[COLOR=blue]AS[/color]
[COLOR=blue]DECLARE[/color]
    @partNum   [COLOR=blue]varchar[/color](40)    
[COLOR=blue]BEGIN[/color]

[COLOR=blue]SET[/color] @partNum = [COLOR=red]'myPart'[/color]

    [COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] @partNum [COLOR=blue]As[/color] PartNumber, 
           BIN_ID,
           SUM(BIN_QTY) [COLOR=blue]AS[/color] BIN_QTY  
    [COLOR=blue]FROM[/color]   plm_brad.LEI_WARE 
    [COLOR=blue]WHERE[/color]  PART_ID = @partNum 
    [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] BIN_ID    
[COLOR=blue]END[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BFT1303]

The query itself produces the proper results. In my C# program, the user is asked to enter a value for the variable myPart.

How do I send that value to my stored procedure so the statement
SET @partNum = myPart
?

THANKS

 

[gmmastros]

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] get_Parts   
	@partNum   [COLOR=blue]varchar[/color](40) 
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]

    [COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color]
           BIN_ID,
           SUM(BIN_QTY) [COLOR=blue]AS[/color] BIN_QTY  
    [COLOR=blue]FROM[/color]   plm_brad.LEI_WARE 
    [COLOR=blue]WHERE[/color]  PART_ID = @partNum 
    [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] BIN_ID    
[COLOR=blue]END[/color]

In your C# program, you'll want to use the command object and also the parameters object. If you don't get a more suitable response, you may be better off posting this question in the C# forum.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BFT1303]

I solved the issue by concat the variable into the command string on the C# side
string cmdPart = "EXECUTE get_Parts '" + @myPart + "';";

mySqlDataAdapter3 = new SqlDataAdapter(cmdPart, mySqlConnection);


Thanks

 

[gmmastros]

Yes. That will work. But there are a couple hidden problems with that approach.

First, if the string/part number you are searching for contains the single-quote/apostrophe character, then your query will fail. While this may be unlikely to occur with part numbers, it's best to get in to the 'proper' habits from the beginning. To accomodate single-quotes within the data, you need to double them. In vb, I would write:

[tt][blue]cmdPart = "EXECUTE get_Parts '" & Replace(MyPart, "'", "''") & "'"[/blue][/tt]

There's also security implemications with this method, too. Do a google search on 'SQL Injection'.

The best method to call stored procedures is by using a command object with parameter objects. It takes a little more coding, but it's actually faster, more secure, and less prone to bugs (more stable).

By getting in the habit of using the command object now, you will save yourself from having to deal with problems later.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[philhege]

What we've done is create a utility object that returns SP results. All we have to do is instantiate the object and pass the SP name and parameter collection.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.