Conditional Where Clause Depending On Input Parameter

May 5, 2008

I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.

My simple QueryA

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL

My simple QueryB

SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL

I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.

Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?

I know it can be done using dynamic SQL construction. But any other ways ?

Also can someone also give in the solution in PL/SQL.

Thanks a bunch.

Jaffery.

View 7 Replies


ADVERTISEMENT

WHERE Clause Boolean - Different Results Depending On Entered Parameter

Jan 13, 2015

This is for Microsoft Server SQL 2012.

I'm trying to create a WHERE clause that will have different results depending on a parameter that is entered. For example, if you put in a number, it will only calculate the rows where the column ID matches that number. However, if you put in 0, which doesn't exist in that column ID, it will instead calculate all the data in the table.

So the below would be a very basic idea of what I'm trying to do, but I'm not sure how to do it with proper syntax.

WHERE IF ID=0, THEN do this
ELSE do this AND ID=#

View 2 Replies View Related

Conditional Format Within Matrix, Depending On Subtotal?

Feb 26, 2007

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

View 3 Replies View Related

Execute Select Depending On The Input Parameters

Nov 14, 2006

Hello, a question please. Could anyone say me if I can create a store procedure with 2 'select's statements into. Besides, I'd want to know if I can execute a "select" depending on input parameters.

Something like this:

create storeproc
Param1, param2

if param1 <> null

Select *
from table
where id = param1

else

Select *
from table
where id = param2

end if

Thanks in advance....







View 2 Replies View Related

SQL 2012 :: Insert ID Column Value Into Table Depending Upon Input Filename

May 19, 2015

I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.

Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport

Location one has three different set files starting with

First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,

This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101

AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3

If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301

after processing all these files in first location, the SSIS looks for files in second location and does the same?

--How to achieve CustID depending upon file name ?

View 0 Replies View Related

Transact SQL :: Usage Of OUTPUT Clause Depending On Temporary Table

Jul 14, 2015

Suppose we have the following table in our database;

CREATE TABLE [dbo].[PERMISSION](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NOT NULL,
[STARTTIME] [smalldatetime] NOT NULL,
[ENDTIME] [smalldatetime] NOT NULL,
[REASON] [nvarchar](250) NULL,
[PERMISSIONTYPEID] [int] NOT NULL,

[code]....

This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;

command.CommandText = @"insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @outID, @outCREATETIME
values(2, getdate(), getdate(), 'sdfg', 1, DEFAULT);";

well, not only this statement gives an error while executing; but also, no such usage defined in the

documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this;
command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime);
insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar

code]....

No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.

View 7 Replies View Related

Conditional Input Params To SP

Feb 2, 2004

I've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?

Dim strWhere
strWhere = " WHERE dbo.""User"".UID IS NOT NULL "

If Not request.querystring("EmployerID") = "" Then
strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'"
End If

If Not request.querystring("AccountNumber") = "" Then
strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'"
End If

If Not request.querystring("LastName") = "" Then
strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'"
End If

If Not request.querystring("FirstName") = "" Then
strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'"
End If

DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr"))
DBCommand = New OleDbDataAdapter _
("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)

Thanks in advance?

View 3 Replies View Related

Setting Comparison Operator Depending On Parameter Value?

Apr 6, 2006

Hello,Is it possible to set a comparison operator using a parameter value?The code below shows what I'm after;declare @co char(1)declare  @date datetimeset @co = '<'set @date = '02/02/2002'select * from recipe where date @co @dateI would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.thanks

View 2 Replies View Related

Conditional WHERE Clause

May 8, 2006

Hi,
[SQL 2005 Express]
I would like a DropDownList to be populated differently depending on the selected value in a FormView.
If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company.  Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.
Here is the SQL for case 1:
SELECT    AdviserID,    AdviserName FROM    Advisers WHERE    (CompanyID = @CompanyID).
Here's the SQL for case 2:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    (CompanyID = @CompanyID) AND    (TypeID = 3).
Here's my best (failed) attempt to get what I want:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    IF @CompanyID = 2 THEN      BEGIN         (CompanyID = @CompanyID)      END   ELSE      BEGIN         (CompanyID = @CompanyID) AND          (TypeID = 3)      END
I've also tried:
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE @CompanyID       WHEN 2 THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND          (TypeID = 3)   END
and 
SELECT    AdviserID,    AdviserName FROM   Advisers WHERE    CASE WHEN (@CompanyID = 2) THEN (CompanyID = @CompanyID)      ELSE (CompanyID = @CompanyID) AND (TypeID = 3)   END
I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.
Thanks very much.
Regards
Gary

View 7 Replies View Related

Conditional Where Clause

Dec 14, 2007

Hi all,

I have a table QT defined as

CREATE TABLE [dbo].[QT](
[Query] [nvarchar](50) NULL,
[Frequency] [int] NULL
) ON [PRIMARY]

Now based on a parameter I want to include a predicate in the select statement.

Basically I am trying to write something similar to the one below but possible only usinf one select statement.

if @queryString is null then

select query ,sum(frequency)

from qt

group by query
else
select query ,sum(frequency)

from qt

group by query
where query = @queryString.

Now is there a way to achieve this thing without using two separate select? The actual code I am trying to write is much bigger and I am trying to see if there is more compact way of expressing things.

Thanks
Aye.

View 7 Replies View Related

Conditional Where Clause Possible?

Jun 11, 2007

Is it possible to use a conditional statements in a where clause?



IE: I have 3 paramaters that may or may not be filled.



I would like to do something along the lines of...





Select * From (tables)

WHERE

If @param1 has value

Begin

'run this where statement

if @Param2 has value

'add this to the where clause

if @param3 has value

'add this to the where cluase



View 10 Replies View Related

Conditional If In Where Clause

Oct 8, 2007



Can I use "CASE WHEN ... THEN ... ELSE ... END" in the where clause of a SQL statement? I have sucessfully used it in the select portion of my statment but I would also like to use conditional criteria in the WHERE portion. Any advice is greatly appreciated.

View 7 Replies View Related

How Can I Use A Conditional Where Clause In Sql Statment

May 15, 2007

I have a store procedure where i need to use conditionel where clause

View 2 Replies View Related

Using Conditional Statement In Where Clause

Mar 26, 2012

I'm trying to use a conditional statement in the where clause.

Here is my table
UID Amount ID PID Amount2
1 30000 8064 NULL NULL
2 30000 8042 8064 30000

What I'm trying to achieve:

If Amount = Amount2 for UID 2 then show UID 1

View 4 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

Performance Issue Using Conditional WHERE Clause

Jan 25, 2008

Consider the following two functionally identical example queries:Query 1:DECLARE @Name VARCHAR(32)SET @Name = 'Bob'SELECT * FROM EmployeesWHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name ENDQuery 2:SELECT * FROM Employees WHERE [Name] = 'Bob'I would expect SQL Server to construct an identical QEP under the hoodfor these two queries, and that they would require essentially thesame amount of time to execute. However, Query 1 takes much longer torun on my indexed table of ~300,000 rows. By "longer", I mean thatQuery 1 takes about two seconds, while Query 2 returns almostinstantly.Is there a way to implement a conditional WHERE clause withoutsuffering this performance hit? I want to avoid using the IF...THENmethod because I frequently require several optional parameters in theWHERE clause.Thanks!Jared

View 6 Replies View Related

Conditional Where Clause W/ Case Statement Possible?

Sep 25, 2007

Greetings,

After many hours search many forums and many failed experiments, I figure it's time to turn to the experts.

I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls.


SELECT uniqueID, lob, xdate
FROM mytable
WHERE

CASE WHEN @myparam = 'ALL'

THEN

xdate >= '2007-09-01'
ELSE

xdate >= '2007-09-01' or
lob = @myparm
END

I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank.

I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much.

Any ideas?

Rob

View 8 Replies View Related

Transact SQL :: How To Get Results Based On Conditional Where Clause

Jul 14, 2015

My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum              PolicyStartdate
123G                       01/01/2012    
456D                       02/16/2012     
789A                       01/21/2012
163J                       05/25/2012

Now my output should return based on 3 parameters..

First two parameters are date range... let say @fromdt and @todt

Third parameter is @policynum

Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output

Scenario-2: enter policy num in policynum param and don't select any dates
Ex:  policynum ='456D'     It returns 2nd row in the output

Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'.  it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)

I need t-sql code to get above results.

View 12 Replies View Related

Conditional Where Clause With Comma Delimited String And Link Table

Jul 24, 2007

 I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports  (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere   if @sports not null    user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!

View 10 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 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 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

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

Passing Path As Input Parameter

Jan 24, 2007

Hi

I have created one SSIS package which extract data from database and put that into verious text files like Emp.txt,Add.txt like that.
Also I set one globle veriable (CityID) that help in extracting data as citywise.
When I ran it through command prompt by passing globle veriable to it like
C:setup pcaSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Data>DTExec /FILE Package.dtsx /SET Package.Variables[CityID].Value;100

it gives me data whose CityID is 100 and format it into text files.
but when i want data for another CityID (101) it overwrites my all previous text files where i kept that files.

Now my requirment is that i want to set another globle veriable that take PATH as input parameter and place these files in that path location.(How i set this path in command promt and also in SSIS)

Please guide me

Thanks

View 1 Replies View Related







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