Transact SQL :: Passing Multiple String Param Values To Stored Proc

Jul 21, 2015

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Now I have to pass multiple param values to it. I was trying this but didnt get any success

exec
[SP_test]'LOC','LOP'

View 10 Replies


ADVERTISEMENT

Passing A Comma Delimited String Of Parameters To A Stored Proc

Jul 30, 2007

Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.

I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?

Thanks.

View 3 Replies View Related

Passing Multiple Selections To A Stored Proc Parameter

Jan 11, 2005

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END
RETURN
END
GO


I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_cri

View 1 Replies View Related

Passing Multiple String Values Separted By A Comma As One Parameter

Oct 16, 2007



Hello,



I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.



Thanks a lot for any feedback!

View 6 Replies View Related

Passing Multiple Values From A Listbox Into A Stored Procedure

Dec 9, 2007

hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e)
{
try
{foreach (ListItem item in authorsListBox4.Items)
{if (item.Selected)
{
AddConfSqlDataSource.Insert();
}
}saveStatusLabel.Text = "Save Successfull: The above publication has been saved";
}catch (Exception ex)
{saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message;
}
}

View 3 Replies View Related

SELECT Returning Multiple Values In A Stored Proc

Jul 20, 2005

HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam

View 6 Replies View Related

How Do I Access The Value Of A Stored Proc Return Param In C# Using ExecuteNonQuery?

Jun 8, 2007

I've got a stored proc to insert a record and return the id of the record inserted in an output param.How do I access this value in my code after the proc is executed?
param = comm.CreateParameter();param.ParameterName = "@MemberID";param.Direction = ParameterDirection.Output;param.DbType = DbType.Int32;comm.Parameters.Add(param);
try{     rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);}catch {     rowsAffected = -1;}
 

View 1 Replies View Related

Output Param Of Stored Proc - Oledb Command

Jun 5, 2007

Hello

This is my first post so please be gentle!!

I have a stored proc that returns a value:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON

GO
-- =============================================
ALTER PROCEDURE [dbo].[spPK]
-- Add the parameters for the stored procedure here
@varNSC varchar(4),
@varNC varchar(2),
@varIIN varchar(7),
@varIMCDMC varchar(8),
@varOut as int output
AS
Declare
@varPK int
set @varPK = 0
BEGIN

--This checks Method 1
--NSC = @varNSC
--NC = @varNC
--IIN = @varIIN
begin
if exists
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varPK =
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varOut = @varPK
if @varPK <> 0 Return
end

[There are some more methods here]

Return

END



How do I get at the output value?

I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.

View 10 Replies View Related

Error Passing Concatenated String To Proc

Nov 8, 2007

Hi,

I have a stored proc which accepts a varchar(255) as a parameter and when I call the proc using a concatenised string I get an error i.e.

-- Proc
CREATE PROCEDURE #proc_param_test
@p_param1 varchar(40) = NULL
, @p_param2 varchar(40) = NULL
AS
BEGIN
SELECT @p_param1, @p_param2
END

EXEC #proc_param_test 'test', 'test 2'
returns
---------------------------------------- ----------------------------------------
test test 2

but EXEC #proc_param_test 'test', 'test 2' + ' - the rest'

gives a Incorrect syntax near '+'. error

The solution must be a real doddle but it's a 'mare to find anywhere.

Cheers,
John

View 4 Replies View Related

Output Param && Multiple Recordests From Stored Procedures

Jul 20, 2005

here's my code:my $sth = $dbhSQL->prepare('{call proc(?,?,?)}');$sth->bind_param(1,"asd");$sth->bind_param(2,"klm");$sth->bind_param_inout(3,$no_go, 1000);$sth->execute;print "no go = $no_go";while(my @row=$sth->fetchrow_array){print "@row";}$sth->finish;Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@iyt varchar(20),@no_go int OUTPUTASSET NOCOUNT ONDECLARE @id_err int,@ans_glue_err intBEGIN TRANSACTIONSELECT user_id FROM myTableWHERE user_id=@id AND iyt=@iytSET @id_err = @@ERRORIF @@ROWCOUNT <> 0BEGINSELECT date,date_mod FROM ans_glueWHERE user_id=@idSET @no_go = 0SET @ans_glue_err=@@ERRORENDELSEBEGINSET @no_go = 1ENDIF @id_err = 0 AND @ans_glue_err = 0BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONENDthe procedure runs perfectly in Cold Fusion, returning both recordsetsand output param, but in perl, it won't print the output param and Idon't know how to access the second recordsetHELP!

View 1 Replies View Related

Passing Value From Stored Proc To Another

Jan 10, 2007

Guys
I have a simple 'black box' proc deriving start and end dates below (the actual values will be derived from more complex code but here for simplicity are hard coded)

create proc upGetDates
as
declare @start datetime ,
@end datetime

select@start = '2001-01-01' ,
@end = '2007-12-31'
go

I want to reuse this proc many times for different reports and using the date values in the calling sp

an example in pseudo code would be

create proc usedates
as
declare@rstart datetime ,
@rend datetime

exec upGetDates @rstart = @start , @rend = @end -- obtaining dates from ---called 'black box' proc
select * from tablename where datecreated >= @rstart and datecreated < @rend
go

Not sure how to 'trap' the dates from the called proc

Can it be done ?

Thanks in anticipation

View 3 Replies View Related

Transact SQL :: Passing Several Values In One Parameter

Nov 12, 2015

In t-sql 2012, I want to run a query where the value is normally an int value. I want to supply a large volume of custID values that are normally int values. I have tried to use a cast  and convert values and that does not work. The query that I am trying to use is the following:

DECLARE  @custID varchar(200)
set @custID = '72793,60546,91069'
select * from table
where in (@custID)

Thus can you show me the t-sql 2012 that I can use to accomplish my goal?

View 12 Replies View Related

How To Display Multiple Values On Report Page From A Multi-value Report Param

Nov 5, 2007

How do I display multiple parameter values on report page from a multi-value report parameter. For example, I have a report parameter where users can select multiple attendance codes and I want them displayed at the top of the report after it's run.

Currently, only the first value is showing on the report.

Thanks.

View 1 Replies View Related

Passing A Column Into A Stored Proc?

Sep 7, 2006

I'm writing a simple voting script and have columns for each options.  I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement?  There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance 

View 2 Replies View Related

Passing Parameters To Stored Proc

Oct 23, 2007



I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement is :

Where actv.ProjID + '-' + actv.Activity = @project

This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!

View 3 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

SQL2005 Passing GETDATE() As A Stored Proc Parameter

May 2, 2008

What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!

View 5 Replies View Related

Passing Boolean To Stored Proc As SQLDBtype.bit Not Working

Mar 17, 2004

Hi I was hoping that someone might be able to help me with this.

I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0

myParm = myCommand.Parameters.Add("@bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)

I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.

Thanks in advance for any advice.

View 4 Replies View Related

Passing Table Variable To Stored Proc / Function

Nov 6, 2002

Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View 3 Replies View Related

Passing Datetime Variable To Stored Proc As Parameter

Jun 12, 2006

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

View 3 Replies View Related

Passing Multiple Values Into A Subreport

May 7, 2008

I am doing report development against OLAP (Cube). I have a parameter which is a multi-value parameter, and I need to pass this as a parameter into a sub-report (pass all the values selected in this multi-value from the main report to sub report parameter).

Is this even possible? If yes how do I achieve this.

Currenty I sent like Fields!Region.Value(0), but I want to send actually Fields!Region.Value() (meaning all selected).

Any help ASAP is appreciated.

View 3 Replies View Related

Passing Multiple Values To A Paramter

Apr 20, 2007

Hi,



I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.

View 1 Replies View Related

Passing Multiple Values To A Single Parameters

Dec 28, 2007

I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?

View 4 Replies View Related

How Do Use Stored Proc Passing Parameter From Table In Selcet Query Statement

Aug 8, 2002

i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.

for example i want to use

select p1 = sp_diff d1,d2 from table1

sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value

View 1 Replies View Related

Passing Multiple Values From Parent To Child Package

Dec 20, 2006

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

View 2 Replies View Related

Populating And Passing Multi-value Param From A Url

Dec 1, 2005

I have a rs report with a parameter(named Site) that is defined as multi-value. What I am trying to do is call the report from an html form with the parameter populated with a comma seperated list of values. The html code is a minimally modified version of the code found in ch. 11 of the hitchhikers guide(code included below after the solid line). If I don't select any items, then execution is fine. 

View 6 Replies View Related

Passing Param - SSIS Package ??

Sep 21, 2007

Hi,


I'm exporting some data from a table to fixed-width file using SSIS.
The query inside the package is:


SELECT col1, col3, col6 FROM Table1
WHERE code = 5 AND status = 'S'


The 'code' value will be passed from the front-end.

How can I make the query dynamically take the 'code' value and export to a file?

If a user selects 'code' as 7 , then the query should change to


SELECT col1, col3, col6 FROM Table1
WHERE code = 7 AND status = 'S'


and with this it has to export to a file.


Is this possible? If so, any suggestions on how to do it? !!!


Thanks,
Siva.

View 9 Replies View Related

SQL Server 2014 :: Passing Multiple Columns Values From Query To One Variable?

Aug 10, 2014

Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)

[code].....

View 1 Replies View Related

Passing Value To Param Of Datetime Datatype While Debugging

Apr 1, 2003

While debugging a stored proc in Sql Query Analyser i have to pass value to stored proc param which is of datatype datetime
i passed it as '03/31/2003'
it's giving error "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification"
if i make it null it's working fine
pls suggest which date format value supposed to supply for datetime param to run debugging successfully

View 7 Replies View Related

Passing XML String To Stored Procedure

Nov 28, 2005

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 Related

Passing Values To A Stored Procedure

Mar 21, 2008

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?

View 4 Replies View Related

String Manipulation Within Stored Proc

Jan 21, 2005

Hi Guys,

I have an nVarChar field named "Event" (I know - I didn't name it !) with variable length values such as *ALARM* or *RESTORE*

They always start or end with a * and I want to trim them off before returning the data to my app.

I've got rid of the first one with...


LTRIM(STUFF(Event, 1, 1, ''))


Any tips on how to get rid of both of them in one go ?

Thanks in advance.

Steve.

View 2 Replies View Related

Problems Passing String Value To Stored Procedure

Aug 2, 2005

Hello, I'm trying to pass a simple string value to a stored procedure and just can't seem to make it work.  I'm baffled since I know how to pass an integer to a stored procedure.  In the example below, I don't get any compile errors or page load errors but my repeater doesn't populate (even though I know for certain the word "hello" is actually in the BlogTxt field in the db.  If I change the stored procedure to say...WHERE BlogTxt LIKE '%hello%'then the results do indeed show up in the repeater.I ultimately would like to pass text from a textbox control or maybe even a querystring to the stored procedure.  Then I'll move on to passing multiple "keywords" to it. :)My relevant code is below.  Thanks in advance for any help.*******************ViewData.ascx.vb file*******************Private strSearch As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadTry Dim objBlogController As New BlogController 'for testing purposes  strSearch = "hello" repeaterSearchResults.DataSource = objBlogController.SearchBlog(strSearch) repeaterSearchResults.DataBind()Catch exc As Exception  ProcessModuleLoadException(Me, exc)End TryEnd Sub----------------------------------------
*******************Controller.vb file*******************
Public Function SearchBlog(ByVal strSearch As String) As ArrayList            Return CBO.FillCollection(DataProvider.Instance().SearchBlog(strSearch), GetType(BlogInfo))        End Function----------------------------------------
*******************        DataProvider.vb file*******************
Public MustOverride Function SearchBlog(ByVal strSearch As String) As IDataReader----------------------------------------
*******************SqlDataProvider.vb file*******************
Public Overrides Function SearchBlog(ByVal strSearch As String) As IDataReader            Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SearchBlog", strSearch), IDataReader)End Function----------------------------------------
*******************Stored Procedure*******************
CREATE PROCEDURE dbo.SearchBlog @strSearch varchar(8000)AS
SELECT ItemID, PortalID, ModuleID, UserID, BlogTxt, DateAdd, DateModFROM BlogWHERE BlogTxt LIKE '%@strSearch%'GO----------------------------------------

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved