Stored Procedure Returns No Rows

[jimbledon]

Hi All,

I have created a simple query that returns some stock items. The next step is to put some input parameters in there to make my page more dynamic.
At the moment i'm testing the stored procedure to return some hard-coded values.

SELECT TblStock.StockNo, TblRange.RangeTitle, TblStock.Colour, TblStock.Unit, TblStock.Width, 
TblStock.CostMtr, TblStock.Backing, TblStock.RepeatPattern, TblStock.Qty, TblStock.DefaultItem, 
TblStock.DefaultStock, TblRange.RangeNo, TblTypeLookup.Type
FROM TblStock 
INNER JOIN TblRange ON TblStock.RangeNo = TblRange.RangeNo
INNER JOIN TblTypeLookup ON TblStock.TypeNo = TblTypeLookup.TypeNo 
AND TblStock.qty > 5 AND TbleStock.StockNo = @StockNo
WHERE (TblStock.DefaultItem = 0) AND (TblStock.DefaultStock = 1) ORDER BY TblStock.CostMtr

This works fine when executed through the query analyser. When i encapsulate it into a stored procedure no results are returned.

CREATE PROCEDURE dbo.SelectStock 

AS
SQL Here!!
GO

Does it matter that i dont have a return statement in the stored procedure. This is something i have never really understood in past; but my simple stored procedures normally work!!


Many Thanks

James

 

[adamroof]

My 2 pennies!

CREATE PROCEDURE dbo.SelectStock 
     @StockNo int    'declare your variable here
AS
BLA BLA SQL
GO

in your aspx....

Sub spRun()
        Dim varGoesHere As Integer = myValue.Text
        Dim myConn As New SqlConnection(ConnectionString)
        Dim myComm As New SqlDataAdapter("SelectStock " & varGoesHere, myConn) 'Make sure have space after sp name in quotes
    
        Dim ds As New DataSet()
        myComm.Fill(ds)
        	dgData.DataSource = ds
        	dgData.DataBind()
        myConn.Close()
End Sub