Input Parameter To Store Procedure

Jan 26, 2004

Hi,


I am using a ListBox where a user can choose multiple lines.


The index of the selected items are then used in a stored procedure.





I wan´t to use the ID´s in this statement:


SELECT * FROM MyTable WHERE MyID IN (1,2,4,9)





But how can I do this?


If I pass them as a string, then I can´t use them as above.


Can I separate the string '1,2,4,9' so I can use them in the statement above?


Or can I send the values as a array to the stored procedure?








Regards!


Jonas

View 5 Replies


ADVERTISEMENT

SQL Server 2012 :: Input Parameter For Store Procedure Like In Statement Value 1 / Value 2

Jun 20, 2014

How can I input parameter for Store Procedure like In ('Value1','Value2')

Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)

View 6 Replies View Related

Creating Store Procedure Accepting Customer ID As Input Parameter

Nov 12, 2015

Display based on customerid display max of item they purchased on a order display only number like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.

using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter.

cid        oid       items
1           1            10
1           2          12
1          3           3
1        4               4

so if we enter 1 as custid it got to give us 12 as the result..

View 7 Replies View Related

How To Write Input Param SqlInt32[] In CLR Store Procedure

Dec 8, 2006

hi,

I want to write a CLR store procedure like this:

public static void SetStateByBatch(SqlInt32[] ids)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlTransaction tran = conn.BeginTransaction();
foreach (SqlInt32 id in ids)
{
try
{
conn.Open();
string commString = string.Format("update dbo.Notification set state=1 where noid='{0}'", id);
SqlCommand comm = new SqlCommand(commString, conn);
SqlContext.Pipe.ExecuteAndSend(comm);
}
catch
{
tran.Rollback();
}
}

tran.Commit();
conn.Close();
}

}

I'know that is not correct,how to resole this problem?

thanks in advance

View 1 Replies View Related

Stored Procedure Input Parameter (asp.net 2.0)

Jun 13, 2006

This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great:
Code:
Dim cmd As New SqlDataSource
cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
cmd.InsertParameters.Add("@firstName", txtFirstName.Text)
cmd.InsertParameters.Add("@lastName", txtLastName.Text)
cmd.InsertParameters.Add("@address1", txtAddress1.Text)
cmd.InsertParameters.Add("@address2", txtaddress2.Text)
cmd.InsertParameters.Add("@city", txtCity.Text)
cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value)
cmd.InsertParameters.Add("@zipCode", txtZipCode.Text)
cmd.InsertParameters.Add("@telephone", txtTelephone.Text)
cmd.InsertParameters.Add("@email", txtEmail.Text)
cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value)
cmd.InsertParameters.Add("@birthday", txtBirthday.Text)
cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@question", txtquestion.Text)
cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
cmd.InsertCommand = "sp_insertCustomer"

cmd.Insert()
Stored Procedure:
CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000)

AS
INSERT tblCustomer
(firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question)

Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question)
 
ERROR:
Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Source Error:
Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:

View 5 Replies View Related

Table Name As An Input Parameter To A Procedure

Feb 25, 2008

Hi
I have this procedure it is creating the proc but when I execute it gives error
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@ID".
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@nextCode".

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetNextAction]

(
@Code char(10),
@Track varchar(30)

)
AS
BEGIN

SET NOCOUNT ON;
Declare @ID int;
DECLARE @SQL1 VARCHAR(2000)
SET @SQL1='Select @ID = Sequence from'+' '+ @Track+ ' where Code=@Code'
EXEC(@SQL1);
Declare @nextCode varchar;
DECLARE @SQL2 VARCHAR(2000)
SET @SQL2 ='Select @nextCode= Code from '+' '+ @Track+ ' where sequence =(@ID+1)'
EXEC(@SQL2);
Declare @NextAction varchar(30);
Select @NextAction= nextAction from [dbo].[CaseStage] where Code=@nextCode;
Select @NextAction;


END
GO
Can someone correct me here
Thanks

View 4 Replies View Related

Help With XML Input Parameter For Stored Procedure

Mar 9, 2008

I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.

Here is an example of the XML being passed:
<object>
<property @propID="14" @propType="4" @propValue="Blah blah text" />
<property @propID="217" @propType="2" @propValue="Some other text" />
</object>

I have a table like this in my database:
CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT)
and I want a stored procedure that will accept XML and update this table.
Here is what I am trying:
CREATE PROCEDURE sp_UpdateObject
@inValues XML
AS
BEGIN
--create a temporary table
DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)

--And then insert the values from the @inValues XML argument into the temporary table
--I am sure the SELECT statement is VERY wrong

INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE)
SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT)
FROM @inValues.nodes('/object/property')

--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID
AND @props.PROPTYPE != 6


END

I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.

I apologize for the long post.
Thanks in advance for any help you can provide.

View 4 Replies View Related

Help For Stored Procedure Input Parameter

Jan 10, 2008

Hi All,

I have a project which will be a tools to edit different tables.

Now I need a stored_procedure to select data from different table.

For example I have a table name "TableFields" which have "tableID","FieldName", "DataType"and so on columns.
It has the following records.
"1","EmployeeID","Varchar"
"1","FirstName","varchar"
"1","LastName","varchar"
"1", "EmployedDate","date"

It has the following records.
"2","AddressID","int"
"2","ApartNo","varchar"
"2", "Address","varchar"


Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data,
"001","Susan","Daka","1999-09-09",
"002","Lisa","Marzs","1999-08-08",
"003","David","Smith","2000-01-01",

I also have address table has columns "AddressID","ApartNo","Address" and has the following data
"1","1101","1208 Mornelle Crt, Toronto",
"2","1209","1940 Garden Drive, Toronto"

I need to create a stored procedure to select data from table "employee " or table "address" or even other tables according to information from "TableFields."
So the table's name can be know as a input parameter, but the fields name will be a list of values and it all depends on tables.

I want to use fields name as a long string separated by",", like I have input "EmployeeID, FirstName,LastName" as an input parameter. But I don't know how to split the string.

Second, I need to create a stored procedure to insert or update data into these dynamically table.

Can anyone help me?

Thanks in advance.




View 8 Replies View Related

Vector As Input Parameter For Stored Procedure

Jul 18, 2006

Hi,

i wanna know if i can use a vector (or an array) for input parameter to a Stored Procedure? Can anyone give me example or links?

thanks

View 1 Replies View Related

Stored Procedure Input Parameter Get Truncated

Jan 23, 2008



I have a stored procedure that takes an input string parameter defined as @name nvarchar(24). The stored procedure takes this string and insert it into a table with the column also defined as nvarchar(24). When I execute this stored procedure with a string of more than 24 characters, the input string somehow get truncated and inserted successfully into the table without giving an error. Why is this the case?

If I simply execute an insert statement with a string longer than 24 characters, it will give me an error message. I tried enclosing the insert statement with a try-catch block in the stored procedure but I still can't trap any error.

Any suggestions?

View 10 Replies View Related

Using Datagrid Selected Value To Input To Parameter In Stored Procedure

Dec 9, 2005

could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE.  I receive my 3 column headers in my datagrid but the parameter isn't doing anything
STORED PROCEDURE
CREATE PROCEDURE USP_Machcusidsearch
@Machcusid nvarchar  OUTPUTAS
SELECT     dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDateFROM         dbo.Machine INNER JOIN                      dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN                      dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemIDwhere machcustomID = @MachcusidGO
VB.NET CODE
Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged
SqlDataAdapter2.Fill(DsMachcusidsearch1)
SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value
DGstatussearch.DataBind()
End Sub
End Class

View 4 Replies View Related

Parameter Store Procedure Not Working

Aug 9, 2006

I have this select statement:
SELECT * FROM [enews] WHERE ([name_nws] LIKE '%" + strSearch + "%' OR
[title_nws] LIKE '%" + strSearch + "%'OR [sub_nws] LIKE '%" + strSearch
+ "%' OR [sum_nws] LIKE '%" + strSearch + "%' OR [content_nws] LIKE '%"
+ strSearch + "%')

This statement works fine but when I turned it into a store procedure,
it returns nothing. I created the store procedure as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE sqlSearch
    -- Add the parameters for the stored procedure here
    @strSearch varchar(250)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')
END
GO

Help is appreciated.

View 1 Replies View Related

How Do I Call A Store Procedure With Parameter?

Aug 9, 2006

I have searched this forum for how to call a store procedure with
parameter and so far no result has answered my question yet. I have the
following store procedure:


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

CREATE PROCEDURE sqlSearch

    -- Add the parameters for the stored procedure here

    @strSearch varchar(250)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    -- Insert statements for procedure here

    WHERE ([name_nws] LIKE '%" + @strSearch + "%' OR
[title_nws] LIKE '%" + @strSearch + "%'OR [sub_nws] LIKE '%" +
@strSearch + "%' OR [sum_nws] LIKE '%" + @strSearch + "%' OR
[content_nws] LIKE '%" + @strSearch + "%')

END

GO

I got this far in my code on how to call the parameter store procedure.
string strConn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlConn; sqlComm.CommandType = CommandType.StoredProcedure; sqlComm.CommandText = "sqlSearch"; sqlComm.Parameters.Add("@strSearch", SqlDbType.VarChar);  I don't know what do next.

Help is much appreciated.

View 4 Replies View Related

Input Parameter Exceeds The Limit Of The Data Type's Length In Stored Procedure

Sep 4, 2007



Hi guys, is there any way to solve my problem as title ? Assuming my stored proc is written as below :

CREATE PROC TEST
@A VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...,5000'

AS
BEGIN

DECLARE @B nvarchar(MAX);
SET @B = 'SELECT * FROM C WHERE ID IN ( ' + @A + ')'

EXECUTE sp_executesql @B
END
GO

View 2 Replies View Related

Wont Execute Store Procedure With Parameter

Oct 4, 2007

Hi
Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on.
It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'.
here is my code: ' build calendar for appointment sessions
Dim Today As Date = Date.Now
Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Dim Conn As New SqlConnection(ConnStr)
Conn.Open()

Dim cmd As New SqlCommand("GetAppointmentSessions", Conn)
cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime)
Dim adapter As New SqlDataAdapter(cmd)



Dim dt As New DataTable

adapter.Fill(dt)

Dim row As DataRow
 Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]

@InputDate Datetime
AS


SELECT TOP (5) uidAppointmentSession, dtmDate,
(SELECT strRoomName
FROM tblRooms
WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room,
(SELECT strName
FROM tblHMResources
WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician,
dtmStartBusinessHours, dtmEndBusinessHours
FROM tblAppointmentSessions

Where
dtmDate > @InputDate

Order By dtmDate  

View 3 Replies View Related

Passing Column Name As Parameter To Sql Store Procedure

Dec 21, 2007

i am using asp.net 2005 with sql server 2005. in my database table contains
Table Name : Page_Content




Page_Id
101
102

1
Abc
Pqr

2
Lmn
oiuALTER PROCEDURE [dbo].[SELECT_CONTENT]
(@lang_code varchar(max))    
AS
begin declare @a as varchar(max)set @a = @lang_code
 
Select page_id,@a From page_content
end
Here in this above store procedure i want to pass 101 to @lang_code
here is my output, but this is wrong output




Page_Id
Column1

1
101

2
101
 but i want following output




Page_Id
101
102

1
Abc
Pqr

2
Lmn
oiu

View 4 Replies View Related

Creating A Store Procedure With Output Parameter

Oct 22, 2005

Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN      //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN      insert into Employee ......      //if error encountered      set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.

View 1 Replies View Related

Sort Expression As Store Procedure Parameter?

Jul 23, 2005

Can I pass a sort expression into a store procedure as a parameter? Iwant to do sth like the following but do not know if it's possible.Maybe I need some tricks to work around?CREATE Procedure SelectAllRoles(@SortExpression varchar(20))ASIF @SortExpression <> ''THENSELECT Id, Name, Name2, LastUpdatedFROM [Role]ORDER BY @SortExpressionELSESELECT Id, Name, Name2, LastUpdatedFROM [Role]GOThanks!Zhu Ming

View 1 Replies View Related

Want To Write Store Procedure That Takes Parameter In SQL

Mar 5, 2008



Some one please tell me how do I write store procedure that receives/takes parameter values
I want to write store procedure which takes ID as a parameter

some one tell me how do I write store procedure that takes parameter
if possibel please show me example of it

iam new to this
thankyou


maxs

View 3 Replies View Related

Reporting Services :: Pass Selected Parameter To Store Procedure Used To Populate Filter

May 12, 2015

I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).

I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .

Now i am using dataset with this store procedure  to fill my @USER_IDS  parameter 

Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error

and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..

View 2 Replies View Related

My Output Parameter Is Being Treated As An Input Parameter...why

Sep 25, 2006

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal.  I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening?  The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){  if (e.Exception == null)    {   string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString();    ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int

Set @stoptrans = 'NO'

/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )

IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN

IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END

IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END

IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END

ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum

SET @NewRetVal = 0
---Successful deletion
END
GO
 --------------------------------------------------------  When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

View 2 Replies View Related

Contact Form - Variable Number Of Input Fields, Store Data As Xml String ?

Jul 13, 2007

Im trying to determine the best way to store data gathered from a form that a user will fill out online.  The form is dynamic and is customized at run time based on group-specific criteria.  The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields.   Now comes the issue of storing this data.  Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field.  So Im considering storing the data as xml.  Has anyone else had to build custom forms and ended up storing the data as xml ?

View 2 Replies View Related

Sorting According To Input Parameter

Jun 1, 2007

Hi, I need to do the following task, which is described by pseudo-code
SELECT * FROM Customers
SORT BY @SortExpression
How can I do something like it (sorting according to input parameter)
Thanks for any idea

View 4 Replies View Related

Need To Get Input In Parameter From End User

May 24, 2007

Hi,

I am trying to make a simple stored procedure which I want to take input on every run from end user:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @sku varchar(20)

insert into skua (sku,SumOfQtyNum)
(select sku,sumofqtynum from sku where @sku = sku.sku)

it is working but not asking parameter values

(I want to use passthrough query after that in Access)
thanks




AA

View 2 Replies View Related

Clearing Parameter Input

Sep 21, 2007

Hi everyone,
I have a question that I believe should be simple to answer yet I cannot find the answer anywhere. I am trying to make it possible for my report to clear the input box whenever the report is run or when anything in a dropdown list is selected. The reason why I want this is because my report has a dropdown list that inputs date ranges for "quick" report info. The other option is to manually type in the begin and end date. If anyone could help me out with this I would be very grateful.

Thanks,
Roy

View 8 Replies View Related

Help With Incorrect Syntax (input Parameter)

Nov 14, 2006

Hi
Help with syntax, I get the error in the line: myDA.Fill(ds, "t1")
Function GetProductsOnDepartmentPromotionPaging(ByVal departmentId As String)
Dim myConnection As New _
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myDA As New SqlClient.SqlDataAdapter _
("MM_SP_GetProductsOnDepartmentPromotion", myConnection)
 
' Add an input parameter and supply a value for it
myDA.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int, 4)
myDA.SelectCommand.Parameters("@DepartmentID").Value = departmentId
Dim ds As New DataSet
Dim pageds As New PagedDataSource
myDA.Fill(ds, "t1")
pageds.DataSource = ds.Tables("t1").DefaultView
pageds.AllowPaging = True
pageds.PageSize = 4
Dim curpage As Integer
If Not IsNothing(Request.QueryString("Page")) Then
curpage = Convert.ToInt32(Request.QueryString("Page"))
Else
curpage = 1
End If
pageds.CurrentPageIndex = curpage - 1
lblCurrpage.Text = "Page: " + curpage.ToString()
If Not pageds.IsFirstPage Then
lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage - 1)
End If
If Not pageds.IsLastPage Then
lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + _
"?Page=" + CStr(curpage + 1)
End If
list.DataSource = pageds
list.DataBind()
End Function
 
Best Regards
Primillo

View 2 Replies View Related

C# Command Timestamp Input Parameter

Mar 27, 2007

I have a stored proc that inserts a customer and it expects a timestamp input parameter. I dont know what a timestamp datatype is for sql 2005 and Ive tried to parse all sorts of data types but the proc errors out saying it needs "Byte[]" which Ive tried. Can anyone help me with this? ThanksRyan 

View 2 Replies View Related

Function That Take A Table As Input Parameter

Feb 26, 2008

hi all
i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently...
i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)...
first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type...
please any help will be appreciated
thanks in advance

View 2 Replies View Related

How To Use A Cookie As An Input Parameter In Sqldatasource?

Apr 8, 2008

i created a cookie as follows
 HttpCookie myCookie = new HttpCookie("Portal");
myCookie["EMail_ID"] = Email_ID;myCookie["Role"] = Role_ID.Value.ToString();myCookie.Expires = DateTime.Now.AddMinutes(1);
Response.Cookies.Add(myCookie);
 
Now i have to take the"EMail_ID" as input parameter in sqldatasourse.
 
<asp:SqlDataSource ID="SqlDS_DC_List" runat="server" ConnectionString="<%$ ConnectionStrings:EnR_Portal_ConnectionString %>"
SelectCommand="proc_DC_List" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:CookieParameter CookieName=" " Name="Email_ID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
 
how can i specify Email ID as cookie name?
 
 

View 11 Replies View Related

Input Parameter In MS SQL 2005 Report

Sep 27, 2007

I am designing a report which need to take parameter input by user when the report is open. Can anyone please tell me how to do this? Using MS SQL 2005 report. Thanks.

View 2 Replies View Related

Updating A Column With Input Parameter?

May 19, 2014

Is it possible to assign to a column a value passed as a parameter?

When I run the proc I get the following error :

Msg 245, Level 16, State 1, Procedure Transfer, Line 17

Conversion failed when converting the varchar value '@ID' to data type int.

----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)

[code].....

View 1 Replies View Related

Set Input Parameter To Zero If Null Or Blank

Jan 8, 2015

I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero

View 1 Replies View Related

Input Parameter Doesn't Show.

Oct 10, 2006

when i pass a input parameter, the SP doesn't recognize it.

like

exec SP 00004

it will print:
select * from table where idname = 4

i want it to print
select * from table where idname = '00004'

how can i do that?

View 11 Replies View Related







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