Combination of Stored Procedure and Text Command Type

[Lethugs]

Hi, is it possible to combine Stored Procedure and Text Command Type in Select Statement?

I have a stored procedure that compute available items, it quite a bit long select statement,
now i need to call this statement in another select statement which is in text command type, how will i do it?

something like

Select Barcode, ItemCode, Get_itemCount From Product

where Get_itemCount is my storedProc

Im using vb.net and Sql server 2008 R2

thanks in advance

[Ancient Dragon]

Do it in two steps. First step, get return value fromt he stored procedure. Second step: format the select statement that includes the return value of the stored procedure. This means making two calls to the database, which can be somewhat time consuming.

Another option might be to create another stored procedure which does all that. The advantage is there is only one call to the database, not two.

[Lethugs]

thanks for the idea however i haven't tried to do the first step, can you please show me example code returning the value of the stored proc then including it in single select statement and make it as a single query.

the second option I think would be not ideal since my query will be very long

thanks again

[Ancient Dragon]

Calling a stored procedure is the same as executing any other sql statement. If you don't know how to do that, then read chapter 13 of this free tutorial

[Lethugs]

I already called a store proc in sql select Statement and as far as I know we include a CommandType.StoredProcedure for it to return a value. how will I add this stored proc in single select statement which now has text as commandType?

I tried this codes but nothing works

Select Barcode, sp_Get_count From Products

and

Select Barcode, Exec Get_count @st = N'Value' From Products

How will I fix the query?

the usual error is near exec, near From, cannot find sp_Get_count stored Proc..

Thanks again for helping

[G_Waddell]

Hi
Does your Stored procedure use an Output Parameter? (Example below:)

CREATE PROCEDURE GETVISITCOUNT @UserID bigint, @NoVisits int OUTPUT 
AS
SELECT @NoVisits = COUNT(*) FROM Visits WHERE (UserID = @UserID)
...

Then in your code you would add the parameters but specify the NoVisits parameter is an output parameter:

Function GetNoOfVisits(byref UserID as integer, ByRef MyConn as SqlConnection) As integer
dim Visits as Integer = 0
Try
    'Set Up Command
    dim cmd as SqlCommand
    cmd.Connection = MyConn

    'You are executing a stored Procedure
    cmd.CommandType = CommandType.StoredProcedure

    'The Text will be the name of the Procedure
    cmd.CommandText = "GETVISITCOUNT"

    'Add Parameters
    cmd.Parameters.Add("@UserID", SqlDBType.BigInt)
    cmd.Parameters.Add("@NoVisits", SqlDBType.Int)

    'Specify Value of Input Parameter
    cmd.Parameters("@UserID").Value = UserID

    'Specify direction of output parameter (default is input)
    cmd.Parameters("@NoVisits").Direction = ParameterDirection.Output

    'Check Connection is open
    If MyConn.State <> ConnectionState.Open Then
        MyConn.Open() 'Open connection
    End if

    'Our output parameter will be populated with the data we need so no dataset required
    cmd.ExecuteNonQuery()

    Visits = cmd.Parameters("@NoVisits").Value

    return Visits
Catch 
    return 0
Finally
    cmd.dispose()

    if MyConn.State <> ConnectionState.Closed then
        MyConn.Close()
    end if

end try

You could then input this result into your next query as a parameter...

cmd2.Parameters.Add("@NoVisits", SqlDbType.Int)
cmd2.Parameters("@NoVisits").Value = GetNoOfVisits(MyUserID)

[Lethugs]

hi,
thanks for the reply, I've tried your code, what is the MyUserID in

cmd2.Parameters("@NoVisits").Value = GetNoOfVisits(MyUserID)?

My data comes from different tables and I guess the function would not work. My required output should be

Code AvailFor Sale AvailStock

G1 25 50
G2 10 15

The code there came from table, and another table contains transactions for every code, like purchased, sold etc. To get the availFor Sales and AvailStock I need to query those transactions tables, like getting the remaining inventory and the query for AvailFor sales and AvailStock are different StoredProc and are quite long, to display all these results I need to create another query which I need to add the results of those storedProc for every code. How will I do this considering those codes comes from my sql table?

thanks again

[G_Waddell]

Sorry,

I based my answer on an example from some code I wrote not on your specifc code.

If you look at my code entries you will see that GetNoOfVisits is a function that takes in a userID and returns the number of visits from the database using a stored procedure with an output parameter. I should have also passed in a sql connection to the function - my bad!

I'm then feeding the result of this function into another parameter on another query.

[Lethugs]

Hi,
I've been trying to do your code, i cant make it til now. how will i feed the data from my select statement to the function,

this is my select statement

Select Code, Size, @sale, @stock From Products - orig select statement

where @sales and @stock should be the result from the functions where it contains the select statement for the stored procedure

How will i feed the code from my select statement to those functions when running it generates error that @sale and @stock is not supplied? (the storedproc also contains the 'Code' in its select statement)

I also tried putting a value to those functions manually, it outputs the result of the function's select statement (the storedproc), not the orig statement with the value of @sale and @stock.

please help

thanks again

[G_Waddell]

OK lets take this slowly, to recap:
1. You want to return a value from a Stored Procedure
2. You want to assign this value to a variable that you then pass into another Stored Procedure or Select Statement

OR are we misunderstanding you?

Select Barcode, ItemCode, Get_itemCount From Product

You said Get_itemCount was the result of your first procedure can I ask what the select statement behind that procedure is? For instance, are you trying to do something like this get the barcode, itemcode and count of orders (or something similar,) from another table for each item?

If so you can do this with a select that uses a nested query:

SELECT itemcode, Barcode, ISNULL(Orders.Item_Count, 0) As Item_Count 
From Product
LEFT OUTER JOIN (
SELECT COUNT(*) As Item_Count, itemcode 
FROM OrdersTable 
GROUP BY itemcode
) As Orders ON Product.itemCode = Orders.itemcode

[Lethugs]

Thank you for helping me solved this out

To clarify this,

1. I have a product table which contains primary info of products including barcode, itemcode, size, Type, etc. ("Product" Table)

2. I have a transaction Table which contains every transaction for every product including purchase, sales order, return, delivery, etc. ("Transaction" Table)

3. Now I need to check remaining product based from Transaction Table which is now in my first storedProc @Get_count

Select Code, (purchase - sales order + return - delivery) From Transaction Table (This if for every product type)

Where those inside the parenthesis are Joined Tables with different conditions mostly from transaction table also, which makes my statement long so I placed it inside storedProc

another storedProc named @Get_all

Select Code, (purchase - sales order + return - delivery) From Transaction Table (This if for every product regardless of type)

The same from the first but regardless with product type

Now on my final query I need to show product info with count of every product considering its type and count of it regardless of its type

Select Barcode, ItemCode, Size, Type, @Get_Count, @Get_all From Product

The barcode in this statement should the also the code for the storedProcs so that I can get the right counts, something like joining table in case i put this all queries in one statement
So the result should be

Barcode ItemCode Size Type @Get_count @Get_all
123 block 20x20 M 10 35
124 block 20x20 L 15 35
156 abc 10x10 T 15 15

Hopes this clarifies my problem

Thanks again

[Lethugs]

Please somebody help me on this

[G_Waddell]

Hi,

OK sorry, been busy at work.

Could you show me the code for the stored procedures?

I think you may have to modify them...

I think I have several ways of solving this but it really depends on how exactly you want this to work... which way suits best

1. Feed the results of each proceedure into each other (if you are only looking at one item at a time this could be the way to go)

2. If you have several items you want to display let SQL do the hard work and look at using views to generate the data.

3. If you have several items you want to view at a time another possibility is to use a dataset and datatables with relationships to display the data. Not recommended if you are bringing back whole tables though.

4. Something else I haven't though of yet...

[Lethugs]

Hi G waddell

Thanks for being supportive, for the ideas you gave, for sharing for precious time even your busy at work, appreciate it. I made some work arounds to accomplish this.

For those who will maybe encounter something related to this, this is how I did it

My StoredProc

Alter Procedure Sales (@in nvarchar(100), @out nvarchar(100) OUTPUT)ASDeclare @result nvarchar(100)My Select Statement here to get CountReturen @resultEnd`

Then in my Vb, I've created a sub to call the sp then show the result with my original select statement.

Private sub Sales

cmd = new sqlcommand("Sales")
cmd.CommandType = CommandType.StoredProcedure
cmd.parameters.add("@in",sqltype.nvarchar)
cmd.parameters.add("@out",sqltype.nvarchar,100)
cmd.parameters("@out").Direction = ParameterDirection.Output 'Calling the sp'

Select Barcode, ItemCode, Size, Type From Product left join etc... 'My orig select Statement'
Using sqladapter to fill datatable

Datatable.Columns.Add("AvailSales").SetOrdinal(4) 'Adding column manually'

Then i looped through this datatable to get the barcode of each record

For i as integer= 0 to datatable.rows.count -1
cmd.Paramater("@in").Value = Datatable.rows(i).Item("Barcode")
cmd.ExecuteNonQuery

Datatable.rows(i)("AvailSales") = cmd.Parameters("@out").Value
Next

'Code for displaying result to datagrid'
End Sub

If there's other better way to do this, that would be very helpful

Thanks again to G Waddell

[ryanjayson]

Try this approach dude; in your Stored Procedure create temp tables (#Get_count and #Get_all)

SQL

CREATE PROC SP_NAME

AS

SELECT Code, (purchase - sales order + return - delivery) AS Count  INTO #Get_count FROM Transaction Table (This if for every product type)
SELECT Code, (purchase - sales order + return - delivery) AS All INTO #Get_all FROM Transaction Table (This if for every product regardless of type)

SELECT Product.Barcode, Product.ItemCode, Product.Size, Product.Type, #Get_count.Count , #Get_all.All 
FROM Product INNER JOIN #Get_count ON Product.ItemCode = #Get_count.Code
INNER JOIN  #Get_all  ON Product.ItemCode = #Get_all.Code

DROP TABLE #Get_count 
DROP TABLE #Get_all