Is it possible to have a stored procedure that passes recordsets which are created and passed from different tables and joins the information in those recordsets to form one group of information?
View 1 Repliesi am using .NET front end and back end SQL Server 2k.
for insert, update, delete and select queries what should i use stored procedure or direct sql through front end.
Help!
I am trying to write a stored procedure that will execute a SQL statement that returns multiple records. Then I need to be able to step through each record, examining a value in one of the fields (and then perform subsequent actions depending on the value).
My problem is that I don't know how to step through a recordset in T-SQL. There doesnt seem to be a "recordset" type object that I can move through. Or can this be done via a cursor? If so, how?
Any help is greatly appreciated!
Thanks!
Decidion
HELP HELP HELP!!
I have two questions.
I'm using VB 6.0 with ADO to SQL 7.0. My stored procedure works fine with Query Analyzer. I pass one parameter.
sp_test "1234"
And I get a recordset returned.
The stored procedure looks like this:
CREATE PROCEDURE sp_test
@facility_key varchar(255) = null
AS
/*DECLARE @sql1 varchar(255)*/
SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key
/*IF @facility_key <> null
PRINT "0"
BEGIN
SELECT * FROM v_reimbursement_report
WHERE facility_key = @facility_key
END*/
When I uncomment the commented lines, I can still get a recordset returned using the query analyzer. And I get the same recordset returned when I use VB ADO and leave the stored proc lines commented out. However, when I call the procedure using the same VB code with the stored proc lines uncommented, I get -1 returned for rs.recordcount:
Private Sub Main_Click()
On Error GoTo Err_Main
Dim lsFacility As String
Dim lsReportName As String
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim gconn As ADODB.Connection
Dim param_facility_key As ADODB.Parameter
Dim gConnString As String
gConnString = "Trusted_Connection=Yes;UID=sa;PWD=yoyo;DATABASE=y ada;SERVER=ya;Network=dbnmpntw;Address=ya;DRIVER={ SQL Server};DSN=''"
Set gconn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
gconn.Open gConnString
cmd.Parameters.Refresh
lsFacility = "1234"
Set param_facility_key = cmd.CreateParameter("facility_key", adVarChar, adParamInput, 255)
cmd.Parameters.Append param_facility_key
param_facility_key.Value = lsFacility
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_reimbursement_report_test"
cmd.Name = "sp_reimbursement_report_test"
Set cmd.ActiveConnection = gconn
rs.Open cmd, , adOpenKeyset, adLockOptimistic
MsgBox rs.RecordCount
Exit_Main:
Screen.MousePointer = vbNormal
gconn.Close
rs.Close
Exit Sub
Err_Main:
Screen.MousePointer = vbNormal
MsgBox "Error " & Err.Number & " has occurred: " & Err.Description
End Sub
Here are my questions:
1. Why don't I get a recordset returned to VB if I have anything (the commented lines) except a simple SELECT statement in the stored proc.
2. Why must I do a Print "0" (or anything) command in the stored procedure within the IF statement to see a recordset return?
TIA for any help you can give....this one has been keeping me up....and my company down.
JWB
Dear all,
I have a big problem in using SQL Server stored procedures:
When I have two select statement in the same procedure, the first one will use for returning specific information for the second one to select appropiate result to the client, but the stored procedure just return the first select statement recordset! What can I do? (I use VB Data Environment to access the stored procedures)
In a nutshell, I am trying to set a combobox's row source using a stored procedure. Surely there is an easy way to do that.
I am working with SQL 2000 as my back-end, and my front-end is an Access Project. The stored procedure I am trying to run is on a different database then the one my project is connected to, but from what I can see in my de-bugging efforts, that is not the problem.
The Stored Procedure;
CREATE PROCEDURE dbo.sp_eeLinksByName
@EmployerNum char(6)
AS
SELECT dbo.TIMS_eeLinksByName.eeLink, dbo.TIMS_eeLinksByName.Employee FROM dbo.TIMS_eeLinksByName
WHERE (dbo.TIMS_eeLinksByName.eeErNum = @EmployerNum)
ORDER BY dbo.TIMS_eeLinksByName.Employee
returns 169 records when I run it directly from the MS Visual Studio environment.
However whe I try to run it from VBA with the following code;
Dim sp_eeLinksByName As String
Dim ConnectionString As String
Const DSeeLinksByName = "SOS-1"
Const DBeeLinksByName = "Insync"
Const DPeeLinksByName = "SQLOLEDB"
Dim objeeLinksByNameConn As New ADODB.Connection
Dim objeeLinksByNameRs As New ADODB.Recordset
Dim objeeLinksByNameComm As New ADODB.Command
ConnectionString = "Provider=" & DPeeLinksByName & _
";Data Source=" & DSeeLinksByName & _
";Initial Catalog=" & DBeeLinksByName & _
";Integrated Security=SSPI;"
' Connect to the data source.
objeeLinksByNameConn.Open ConnectionString
' Set a stored procedure
objeeLinksByNameComm.CommandText = sp_eeLinksByName
objeeLinksByNameComm.CommandType = adCmdStoredProc
Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn
' Execute the stored procedure on
' the active connection object...
' "CurrTSCalendar" is the required input parameter,
' objRs is the resultant output variable.
objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs
' Display the result.
'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: "
Select Case objeeLinksByNameRs.RecordCount
Case 0
'Do Nothing
Case Is > 0
'Get the Employee List
objeeLinksByNameRs.MoveFirst
Do While Not objeeLinksByNameRs.EOF
MyControl.AddItem (objeeLinksByNameRs.Fields("eeLink") & ";" & objeeLinksByNameRs.Fields("Employee"))
objeeLinksByNameRs.MoveNext
Loop
End Select
'Clean up.
'objRs.Close
objeeLinksByNameConn.Close
Set objeeLinksByNameRs = Nothing
Set objeeLinksByNameConn = Nothing
Set objeeLinksByNameComm = Nothing
I get an "Object Variable or With Blick Vraiable not Set"...... for the life of me I do not know why? Does anyone have any thoughts?
The stored procedure I created returns all records from the table if no parameters are passed, as expected. When I pass in the only parameter, I get 0 records returned when there should be one or more returned. I'm sure it's something simple in my syntax, but I don't see it.
This call returns all records:
exec webservices_BENEFICIAL_USES_DM_SELECT
This call returns 0 records, when it should return 1:
exec webservices_BENEFICIAL_USES_DM_SELECT @DISPOSAL_AREA_NAME='Cell 8'
Here is the stored procedure:
ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT]
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @DISPOSAL_AREA_NAME IS NULL
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
END
ELSE
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
WHEREDISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME'
END
END
Hi All. My question is this. I have a complex stored procedure in SQLServer which works fine when I run it in Query Analyzer. However, whenI call it within my ASP script, it returns nothing, and sometimes locksup. If I change my script to call other existing stored procedures itworks fine. It's just with this particular stored proc. I have triedvarious DB calls in ASP, such as opening the recordset through an ADOconnection and through the Command object but to no avail. Here is mySQL:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOALTER PROCEDURE dbo.sp_getProfessionalsByTypeID@typeID intASdeclare @catID intdeclare @userID intdeclare @strUserName varchar(100)declare @strFirstName varchar(100)declare @strLastName varchar(100)declare @strTypeName varchar(100)declare @strCategoryName varchar(100)declare @strPictureURL varchar(100)declare @sql varchar(1000)declare @a varchar(100)declare @b varchar(100)declare @c varchar(100)declare @d varchar(100)beginset @a=''set @b=''set @c=''set @d=''--Create Temp TableCREATE TABLE #QueryResult (nCatID int, nTypeID int, nUserID int,strUserName varchar(100), strFirstName varchar(100), strLastNamevarchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100))--Search QuerybeginINSERT #QueryResultSELECTdbo.tbl_musician_type.nCatID, dbo.tbl_musician_type.nTypeID,dbo.tbl_users.nUserID, dbo.tbl_users.strUserName,dbo.tbl_users.strLastName,dbo.tbl_users.strFirstName,dbo.tbl_musician_type.strTypeName, dbo.tbl_category.strCategoryName,dbo.tbl_professionals.strPictureURLFROMdbo.tbl_musician_type INNER JOINdbo.tbl_category ON dbo.tbl_musician_type.nCatID= dbo.tbl_category.nCategoryID INNER JOINdbo.tbl_profile ONdbo.tbl_musician_type.nTypeID = dbo.tbl_profile.nTypeID INNER JOINdbo.tbl_users ON dbo.tbl_profile.nUserID =dbo.tbl_users.nUserID LEFT OUTER JOINdbo.tbl_professionals ON dbo.tbl_users.nUserID= dbo.tbl_professionals.nUserIDWHEREdbo.tbl_musician_type.nTypeID = @typeIDend--Create Temp TableCREATE TABLE #QueryResult2 (ID int IDENTITY,nCatID int, nTypeID int,nUserID int, strUserName varchar(100), strFirstName varchar(100),strLastName varchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100), strArtist varchar(100),strAlbumTitle varchar(100), strRecordLabel varchar(100), strYearvarchar(100))--Now Declare the Cursor for Speakersdeclare cur_musicians CURSOR FOR--Combined Results Groupedselect distinct nCatID, nTypeID, nUserID, strUserName, strLastName,strFirstName, strTypeName, strCategoryName, strPictureURLFrom #QueryResultopen cur_musiciansfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURL--Loop Through Cursorwhile @@FETCH_STATUS = 0beginSELECT TOP 1 @a = strArtist, @b=strAlbumTitle,@c=strRecordLabel, @d=strYearFROM dbo.tbl_profile_discogwhere nTypeID = @typeID AND nCategoryID = @catID AND nUserID =@userIDinsert #QueryResult2select @catID as nCatID, @typeID as nTypeID, @userID as nUserID,@strUserName as strUserName, @strLastName as strLastName, @strFirstNamestrFirstName, @strTypeName as strTypeName, @strCategoryName asstrCategoryName, @strPictureURL as strPictureURL, @a ashighlightArtist, @b as highlightAlbumTitle, @c as highlightRecordLabel,@d as highlightYearfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURLset @a = ''set @b=''set @c=''set @d=''endselect * from #QueryResult2 TI--Clean Upclose cur_musiciansdeallocate cur_musiciansdrop table #QueryResultdrop table #QueryResult2endGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 5 Replies View RelatedHi All,
I'm hoping somebody can help me with this as it is driving me mad. I've created a stored procedure which returns Employee information recordset when the windows username is passed to it as a parameter. I want to then store this information in Session variables so content can be filtered depending on employee status, but when I execute my code no records are returned. I know for a fact that the stored procedure works because I bound it sqldatasource and displayed results from it in a Datalist and tested it in sql server. My code is as follows can anybody see any problems with it, in runs through fine with but when I try a read a field from the datareader in says there is no data to read.
Dim CurrentUser As String, Pos1 As Int16, EmployeeId As Int32
Dim cn As New System.Data.SqlClient.SqlConnection
Dim param As New System.Data.SqlClient.SqlParameter
Dim reader As System.Data.SqlClient.SqlDataReader
Dim cmd As New System.Data.SqlClient.SqlCommand
CurrentUser = CStr(User.Identity.Name)
Pos1 = InStr(CurrentUser, "") + 1
CurrentUser = Mid(CurrentUser, Pos1)
Session("User") = CurrentUser
Session("CID") = Nothing
cn.ConnectionString = "Data Source=LAPTOP-4SQLEXPRESS;Initial Catalog=SCMdb;Integrated Security=True"
cn.Open()
cmd.Connection = cn
cmd.CommandText = "CurrentUser"
cmd.CommandType = CommandType.StoredProcedure
param = cmd.CreateParameter
param.ParameterName = "@UserName"
param.SqlDbType = SqlDbType.VarChar
param.Value = CurrentUser
cmd.Parameters.Add(param)
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
EmployeeId = reader.Item("EmployeeID")
reader.Close()
Any help would be much appricated this is driving me mad.
Thank You
Shaft
I'm using the ADO command object to call a stored procedure in SQL Server from Access 2000.
Set rst = cmd.execute
I receive the correct data back but I can't edit the recordset or addnew.
Is this possible????
Thanks
I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.Here is my code:
<%
......
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()
if not logRS.EOF then
......
%>
My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.
I have a recordset returned from a stored procedure executed in the form open event. Could this recordset append to the form's recordsource property in the form's open event in VB? if so, what's the syntax?
Private Sub Form_Open(Cancel As Integer)
dim ...
Set add_bag_results = Nothing
With add_bag_results
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "spSampling_add_bag_results"
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
'.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, rs_add_bag_results(1))
.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n)
..
Set rs_add_bag_results = .Execute
End With
Me.RecordSource = rs_add_bag_results ?
Thanks!
can i pass recordset object(which have 10 records) from vb6 to stored procedures, if yes what is the datatype i have to declare in stored procedure
View 8 Replies View RelatedI have successfully Create a Site, Inserting Updating and
Deleting information in my DB all with Stored Procedures, But I need the
ability to pass their username into my Stored Procedures. How and where do I
code this in my ASPX file?
My SP would be something like this
Create procedure test
@UserName
varchar(50)
As
Select *
From table
Where username
= @username
All of the
data is tied to the user in one way or another but I do not know what code to
put in my page?
Anyone got an example of passing xml to a stored procedure and within that procedure, grabbing values out of the xml to perform an insert ?
View 1 Replies View RelatedI have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors.
Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors.
The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails.
SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity()
END
and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID)
SELECT @NewpublicationID
Thanks
Gemma
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
View 2 Replies View RelatedHi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
View 8 Replies View RelatedHey huys, I got this stored procedure. First of all: could this work?
--start :)
CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0'
AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)
then I got this VB.Net code in my ASP.net page....
---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed
End Sub
---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
I want to pass and array of ids to a procedure for inserting i a relation table.
I found some examples in other posts, but had problems getting them to work.
I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations.
(I´m using sql server 2000), had some problem with examples with strpos then.
Any hints ?
peace.
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View 1 Replies View RelatedHi, that's the problem:
I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields.
When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler:
exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1'
The fields are defined as follows:
<SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters>
The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'.
I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.
Thanks in advance,
Anderson
I have the following stored procedure
ALTER PROCEDURE dbo.TextEntered
@searchString varchar(30)
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN
doesn't work!!!!
I would like to produce the following where s is @searchString:
Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME
Any ideas how I might acomplish this I have tried almost everything !
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View 11 Replies View Relatedmake it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Any help is GREATLY appreciated. Thanks.
Robert
Hello, I'd greatly appreciated if someone can help me out on this problem:
I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:
exec sp_GetLN "smith, lee, jones, hanson"
In the stored procedure:
@strLN varchar <- "smith, lee, jones, hanson"
/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/
SELECT * from tblCustomers
WHERE LASTNAME IN (@strLN)
It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB??
Your help is greatly appreciated~
Thank you
Hi all,
I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement.
Thank you for all the help you can give me.
Regards,
Eve
I am using VB6 and SQL Server 6.5. I want to pass a boolean to a SQL Server stored procedure. The code below is the main part that tries to pass 'True' to the stored procedure, but an error occurs when the line rst.Open.... is reached ? The error is as follows: The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
I have tried putting 'True' directly in the rst.Open... line instead of assigning it to a boolean variable but the same error occurs.
The stored procedure works fine when run in SQL Server.
Can anyone help ? Please.
complete = True
Set rst = New ADODB.Recordset
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockReadOnly
rst.Open "usp_CompleteUID_sel (" & complete & ")", UIDCon, , , adCmdStoredProc
Do While Not rst.EOF
lst_pastUIDs.AddItem (rst.Fields(1))
rst.MoveNext
Loop
rst.Close
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
Hi, I'm fairly new to TSQL so I'll try to explain as best I can.
I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(8)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode = @MyLsn)
The value I'm passing (@MyLSN) is 24/12359 (one LS Number)
The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:
CREATE PROCEDURE ExplodeDilutions
@MyLSN AS VarChar(50)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.*
FROM TSample
WHERE (TSample.ISmpShortCode IN (@MyLsn))
The value I'm passing (@MyLSN) is '24/12359','24/12360'
This is where the problem begins because the stored procedure now doesn't return a recordset.
If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned.
Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.
So if someone could point me in the right direction then that would be much appreiciated.
Thanks in advance.
I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.
It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?
EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?