What's The Best Way To Create A Stored Procedure That Queries With Multiple Parameters?

Nov 21, 2007

 

If I were to create a stored procedure that searches a table using (optional) multiple parameters, what would be the best way to do the search.  I want to try and avoid using several "IF" statements (like IF @FirstName IS NOT NULL, etc).  How would I do it, or would I just be better off using several "IF" statements?  Thanks...

 CREATE PROCEDURE intranet_search_GetEmployeesBySearch
(
@FirstName NVarChar(100),
@LastName NVarChar(100),
@Phone NVarChar(50),
@Cell NVarChar(100),
@Pager NVarChar(100),
@Ext NVarChar(50),
@Email NVarChar(100),
@Department NVarChar(200),
@Position NVarChar(100),
@IsManager Bit
)
AS
BEGIN

SET NOCOUNT ON;




END
GO 

View 8 Replies


ADVERTISEMENT

Creating A Stored Procedure With Parameters And Multiple Sql-server Queries

Jan 23, 2008

I need to create a stored procedure that will have about 10-15 queries and take 3 parameters.
 the variables will be: @lastmonth, @curryear and @id
@lastmonth should inherit Session variable intlastmonth
@curryear should inherit Session variable intCurrYear
@id should inherit Session id
 One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "'  AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id")
The rest of the queries will be similar and use all 3 variables as well.
How can I go about this and how will queries be seperated.
 

View 2 Replies View Related

SQL Server 2012 :: Multiple Queries In Same Stored Procedure

Sep 16, 2015

Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.

They tend to look like this:

create procedure UberProc (@QueryId varchar(50))
as

if @QueryId = 'First Horrible Idea'
begin
select stuff from something
end
if @queryid = 'Second really bad idea'
begin
select otherstuff from somethingelse
end

I see the following problems with this practice:

1) SQL can't cache the query plan appropriately
2) They are harder to debug
3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.

View 9 Replies View Related

Using Multiple Distinct Queries Inside Single Stored Procedure

Feb 21, 2008

Hello,

I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella.

For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So:

IF @QUERY_ID = 1
BEGIN
SELECT [whatever]
FROM [tbl1]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
ELSE
IF @QUERY_ID = 2
BEGIN
SELECT [whatever]
FROM [tbl2]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
END

I hope that makes sense. Thanks in advance.

View 7 Replies View Related

Multiple Parameters To A Stored Procedure

Sep 6, 2006

Hi All,

I have a database with very heavy volume of data.

I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.

Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.

Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?

Thanks in advance,

HHA

View 4 Replies View Related

Pass Multiple Parameters To Stored Procedure

Mar 26, 2008

Hi,
I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample,
CREATE PROCEDURE [dbo].[display_customer]
@agentID varchar(20), 
@customer1 varchar(20),
@customer2 varchar(20),
.....            -- Here I do know how many customers for each agent
AS
SELECT  name, city, state, zip
FROM rep_customer
WHERE agent = @agentID and (name = @customer1 or name = @customer2)
Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure?
Thanks a lot!
 

View 6 Replies View Related

Need Help In Understanding Multiple Parameters Sent To A Stored Procedure

Apr 12, 2006

OK, 1st, I have looked at every article that has come back on a "Stored Procedures" Search on this site, and am more confused than when I started looking for my answer.
This is what I need to do:
I need to pass a search sentence to a stored procedure, have the stored procedure break up the space delimited string and then do a "like" and "contains" in the WHERE statement, on what was sent to the stored procedure, and then return the results to a gridview for the person to select which item best answers their search.
I am just totally lost with using a stored procedure. I have done this in webmatrix when I coded it all into the aspx page, or into the codepage, but I have never done it with a stored procedure on the sql server, never sent a varible to a stored procedure... and am totlaly lost, or just do not understand how to do it.
Any help would be great.
Thanks in advance.
D4D

View 2 Replies View Related

How To Execute A Stored Procedure With Multiple Parameters In VB

Aug 3, 2012

Here is my stored procedure:

ALTER PROCEDURE dbo.SP_UpdateFixedRev
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(
@Number int,
@FixedRev money
)
AS
BEGIN
/* SET NOCOUNT ON */
Update Ticket set FixedRev = @FixedRev where Number = @Number;
End

Here is my code:

Dim dbConn As New OleDbConnection
Dim dbComm As OleDbCommand
dbConn.ConnectionString = connStr 'connStr is class-level vrbl
dbConn.Open()
dbComm = dbConn.CreateCommand
dbComm.Parameters.Add("@Number", OleDbType.Integer).Value = txtDatabaseTicketNo.Text
dbComm.Parameters.Add("@FixedRev", OleDbType.Currency).Value = txtFixedRev.Text
dbComm.CommandText = "SP_UpdateFixedRev"
dbComm.CommandType = CommandType.StoredProcedure
dbComm.ExecuteNonQuery()
dbConn.Close()

However its not updating my database when I run the app from a button click event.

View 7 Replies View Related

Passing Multiple Parameters To Stored Procedure Using SqlDataSource

Oct 9, 2007

Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan 

View 2 Replies View Related

What Is Wrong With This Code? Selecting Data With Stored Procedure With Multiple Parameters

Sep 22, 2005

oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent)  'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.

View 2 Replies View Related

How To Use A Stored Procedure With Parameters To Create The Datasource View For Report Model Builder

Mar 12, 2007

I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?

View 2 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

SQL Reporting Services Issue Using Multiple Parameters Where Data Is Passed In From A Stored Procedure

Apr 15, 2008



I have an issue with using multiple parameters in SQL Reporting services where data is passed in from a stored procedure



When running the report in design mode - I can type in a parameter sting and it runs fine



In the report preview screen I can select single parameters by ticking the drop down list and again it runs fine



as soon as I tick more than one I get an error



An error occurred during local report processing

Query execution failed for data set €˜data'

Must declare the scalar variable '@parameter'



Some info...



The dataset 'workshop' is using a sproc to return the data string?

I get multiple values back fine in the sproc using this piece of code

(select [str] from iter_charlist_to_table( @Parameter, DEFAULT) ))



I have report parameters set to Multi-Value

Looking through the online books it says...



You can define a multivalued parameter for any report parameter that you create.

However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.

Am I trying to do the impossible ?

View 1 Replies View Related

SQL Server 2012 :: Create XML File From AS400 Stored Procedure Returning Multiple Datasets

Oct 3, 2014

I have a store procedure in MC400 which I can call from SSMS using the below command:

EXEC ('CALL GETENROLLMENT() ')At serverName

Now this command returns two data sets like:

HA HB HC HD HE
1112
112571ABC14
113574ABC16
114577ABC87
DADBDCDD
1115566VG02
1115566VG02
1115566VG02

I want to generate two different XML files from these two datasets.Is there any way this can be achieved in SSIS or t-sql ?

View 3 Replies View Related

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

Mar 12, 2008

Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlDbType

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")

Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)

testCMD.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)

RetValue.Direction = ParameterDirection.ReturnValue

Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)

auIDIN.Direction = ParameterDirection.Input

Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)

NumTitles.Direction = ParameterDirection.Output

auIDIN.Value = "213-46-8915"

PubsConn.Open()

Dim myReader As SqlDataReader = testCMD.ExecuteReader()

Console.WriteLine("Book Titles for this Author:")

Do While myReader.Read

Console.WriteLine("{0}", myReader.GetString(2))

Loop

myReader.Close()

Console.WriteLine("Return Value: " & (RetValue.Value))

Console.WriteLine("Number of Records: " & (NumTitles.Value))

End Sub

End Class

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.

Thanks in advance,
Scott Chang

View 29 Replies View Related

2 Different Queries In One Stored Procedure

Dec 3, 2003

Hi ya'll! First off, please let me know if I'm totally doing the wrong thing here ...

I have set up two different simple queries in one stored procedure that I'd like to reference from one fetch from my ASP.Net/VB.Net web template. Can I do this? I think I can, 'cause most of the data is showing up.

Code follows:
[stored procedure]

CREATE PROCEDURE dbo.sp_admin_vStatistics
AS
BEGIN

SELECT COUNT(metric_ID) AS device_totalRequests, MAX(request_due_date) AS device_maxRequest_due_date, MIN(request_due_date) AS device_minRequest_due_date,
MAX(metric_log_dtTime) AS device_maxLog_dtTime,
FROM dbo.metrics

SELECT COUNT(user_ID) AS user_total, MAX(user_lastlogin_dtTime) AS user_LastLogin
FROM dbo.users

END
GO


[snippet of .Net page where I'm actually trying to display the output]

'Loop over our query results & print it
Do While (dr.Read())
lblUser_Total.Text = CType(dr("user_total"), Integer)
lblUser_LastLogin.Text = CType(dr("user_LastLogin"), Date)
lblDevice_TotalRequests.Text = CType(dr("device_totalRequests"), Integer)
lblDevice_minRequest_due_date.Text = CType(dr("device_minRequest_due_date"), Date)
lblDevice_maxRequest_due_date.Text = CType(dr("device_maxRequest_due_date"), Date)
lblDevice_minLog_dtTime.Text = CType(dr("device_minLog_dtTime"), Date)

Loop


My output shows nothing for user_total and user_lastlogin, but the other "device" information gets properly displayed. Running SQL Query Analyzer shows it all. I just think I'm referencing it incorrectly.

Any suggestions? Thanks in advance for any ideas.

Brent

View 3 Replies View Related

2 Queries, One Stored Procedure

Jan 23, 2008

I am trying to create a stored procedure to be called from an ASP.Net page.

There are two sets of stats that I want to be able to pull off for a given ID - firstly a list of all the names given and the number of times that name has been given and secondly a list of all the reasons given and the number of times for each reason.

With the queries there is clearly different Group By required to get the necessary stats off.

I dont want to have to make two round trips to the server to get the two different results but cannot see how to otherwise to get the results out and into ASP.Net to consume?

Any ideas anyone?

View 2 Replies View Related

Creating A Stored Procedure From 3 Queries

Jul 20, 2005

Hi all,Sorry for HTML, there is a lot of code & comments I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :( Please help me figure out what stops it mid way? I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*): Here is my stored procedure:CREATE PROCEDURE csp_AuthorAccountInfo@CandidateID int,AS DECLARE @ScriptsNo int, @ManuscriptID int SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @CandidateID/* this is where it stops all the time :(Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/SELECT c.*, l.LocationID, @ManuscriptID=m.ManuscriptID, l.State, cn.Country FROM Candidates c INNER JOINManuscripts m ONc.CandidateID = m.CandidateID INNER JOINLocations l ON c.LocationID = l.LocationID INNER JOINcn ON l.CountryCode = cn.CountryCodeWHERE c.CandidateID = @CandidateID/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.CommentsFROM Manuscripts m INNER JOINManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOINManuscriptStageNames msn ON ms.StageNameID = msn.StageNameIDWHERE m.ManuscriptID = @ManuscriptIDORDER BY ms.DatePosted DESCGO

View 2 Replies View Related

Three Queries Of One Table As One Stored Procedure?

Nov 1, 2007



Hi,
I currently have three queries running seperately which I'd like to join up..

However I'm sure it can be done..

Here goes:

I have one table which lists payments made (it stores a PaymentID from another table, a payment amount and an invoiceID)

Therefore there can be several records with the same PaymentID referenceing different invoices (i.e a user paying off several invoices with one payment)

I have one query which lists all of the invoices paid against one payment.

SELECT PaymentID, InvoiceID, PaymentAmount WHERE PaymentID = xxx AND PayType <> 'Credit'

I then have a second query which is ran against each individual invoice which shows the other payments which have been made against this invoice already


SELECT PaymentID, InvoiceID, PaymentAmount WHERE PaymentID <> xxx AND PayType <> 'Credit' AND InvoiceID = XXX


and final I have one query which lists the credits
SELECT PaymentID, InvoiceID, PaymentAmount WHERE PayType = 'Credit' AND InvoiceID = XXX


All of the above lets me see an payment, which invoices have been paid against that payment.. and then for each invoice, any other payment which were made beforehand, and finally any credits against that invoice.


I run these from an ASP page in a loop which is pretty inefficient way of doing it.

I would much prefer to amalgamate the three queries above so I could see what I was paying now, what had already been paid and what was credited against each invoice from a PaymentID.. all in query.

Is that possible?

Thanks
mtm81


View 5 Replies View Related

Execute Stored Procedure From Stored Procedure With Parameters

May 16, 2008

Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY

CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC

Any assistance would be greatly appreciated.
Thank you,
Dave

View 6 Replies View Related

SQL Stored Procedure Can't Insert , Parameterized Queries...

May 16, 2004

I have a SQL stored procedure like so:

CREATE PROCEDURE sp_PRO
@cat_num nvarchar (10) ,
@descr nvarchar (200) ,
@price DECIMAL(12,2) ,
@products_ID bigint
AS
insert into product_items (cat_num, descr, price, products_ID) values (@cat_num, @descr, @price, @products_ID)


when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1

I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.

View 3 Replies View Related

All Select Queries From Stored Procedure Not Appearing Under Dataset

Jan 29, 2008

I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.

But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.

i am not seeing the remaingin 3 resultsets, on the reports dataset.

Is it possible on the reports or not.

In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.

Thank you all very much for the information.

View 1 Replies View Related

Optimizing Queries / Stored Procedure For Retreiving Millions Of Rows

Apr 22, 2006

I am having one querry regarding the same line.
In my stored procedure i am fetching the data from one table containing upto 5 to 6 million rows I made use of index in my database but then also I cant optimise my execution time of that sp.
Please help me out of this problem.

View 1 Replies View Related

Multiple Insert Into Multiple Tables With A Stored Procedure

Mar 1, 2007

Hello
I am building a survey application.
 I have 8 questions. 
 Textbox -  Call reference
 Dropdownmenu  - choose Support method
 Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
 

View 9 Replies View Related

Stored Procedure Parameters

Oct 3, 2006

Hi,I have a problem with a call to stored procedure on SQL server. I created stored procedure for inserting a row to a table.In my application I read data from ASP.NET form and prepare parameters for calling my stored procedure. Like:            SqlCommand sc = new SqlCommand("AddToMyTable");            try            {                sc.CommandType = CommandType.StoredProcedure;                sc.Connection = new SqlConnection("myconnectionstringhere");                SqlParameter param;                param = sc.Parameters.Add("@MyFirstParam", SqlDbType.NVarChar,50);                param.Value = "something";                 ..... here I repeate "add" and value assignment for all my parameters            }...When I call ExecuteNonQuery(); I get exception that one of the parameters for stored procedure is expected but not supplied. However, I can find this very same parameter in my source code to be added to list of parameters. I think my problem is caused by large number of parameters. I have 55 parameters, half of them are of type nvarchar(50) and half of them of type bit. My reasoning is based on the fact that if I remove parameter that is reported to be missing from my stored procedure and from my application, I get the same error with next parameter in the list.Where should I increase maximum number of parameters that are allowed? Do I have some buffer problem? Where to specify that I need more space for parameters? Nenad 
 

View 10 Replies View Related

A Stored Procedure With Many Parameters

Jan 1, 2008

There is a form in my project which users can add their emails through it. The maximum numbers of emails are 60. I put a textbox and a button for adding email. But I don’t know which of the following solutions is suitable:
1.       After each click by user , the insert stored procedure will be called
2.       All the emails entered by users will be saved and then, the insert stored procedure will be called. This SP must have a loop but I am not sure about the right code for this SP because I have to define 60 input parameters for this SP.
 

View 3 Replies View Related

Stored Procedure Parameters

Mar 31, 2005

Hi
what's the difference between using parameters in these two ways:
SELECT
@PortalName = Portals.PortalName ...
and
SELECT
Portals.PortalName...
first one will NOT be a result set, and second one will
Whats the basic difference between that? I need to use a NextResult method within a .cs filebut it is not working
thanks

View 1 Replies View Related

Regarding Stored Procedure Parameters

Jun 29, 2007

I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which having all 11 parameters.
If I don’t enter any one of the value I am not getting the result and it will goes on exception handling.

Now what I want is even I enter one value this must be excute………….i think some validations should be done in
Stored procedure's 'where' clause.but I don’t know how

i would be very happy if someone guides me with sample code for this.

View 7 Replies View Related

Stored Procedure Parameters

Jun 16, 2008

I know there ar in parameters and out parameters, but are there any other kinds? Are there in/out parameters? If so, how would you declare one in the stored procedure?

Thanks.

View 8 Replies View Related

Stored Procedure With Parameters

Dec 6, 2006

Hi, I am using isqlw from SQL Server 2000. Is it possible to create T-SQL script that can receive input parameter from outside?

Example, my stored procedure is:

create my_sp @inputvar varchar(12)
as
select @inputvar
go

then I will create an .sql (such as mysql.sql) file that contains line:
exec my_sp parameter

next, I would like to call this .sql file using:
isqlw.... -i mysql.sql parameter?? -o output.txt

is it possible to do that way? Or is there any other way to do so?

Thanks a bunch,
nicoart.

View 8 Replies View Related

Regarding Stored Procedure Parameters

Jun 29, 2007

I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters.
If I don’t enter any one of the value I didn’t get the result and it is raising exceptions.

View 3 Replies View Related

Parameters In Stored Procedure

Feb 14, 2008



Parameters are passed to Stored Procedure from VB.NET Application like

Dim param As SqlParameter
param = New SqlParameter("@name",DbType.String)
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)

But is there any way to specify parameters without specifying the
parameter name and type like

Dim param As SqlParameter
param = New SqlParameter
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)

But when i try like this i am getting an error saying that "Parameter1 is not a parameter in the procedure"...
Can anybody tell me how to solve this .
Thanx in advance

View 5 Replies View Related

Calling Three Different Variable Coming From Three Different Sql Queries Which Defined In Same Stored Procedure From DataAdapter

May 22, 2008

 
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view.
 
Simply I want to below statement in stored procedures and call from program.  Can any one help me plz.
 
 
 
 
con = DataBaseConnection.GetConnection();
 
DataSet ds = new DataSet();
           
SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da.Fill(ds,"Dep");
           
SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con);
da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da1.Fill(ds,"Exp");
           
           
SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con);
da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da2.Fill(ds,"Inc");
 
string deposits = Convert.ToString(ds.Tables["Dep"].Rows[0].ItemArray[0]);
string expenses = Convert.ToString(ds.Tables["Exp"].Rows[0].ItemArray[0]);
string income = Convert.ToString(ds.Tables["Inc"].Rows[0].ItemArray[0]);
           
GridView1.DataSource = ds;
GridView1.DataBind();
GridView1.Rows[0].Cells[0].Text = "Total";
GridView1.Rows[0].Cells[1].Text = deposits
GridView1.Rows[0].Cells[2].Text = expenses;
GridView1.Rows[0].Cells[3].Text = income;
//The above program was success.
 
// This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u.
SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString();
 
cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString();
 
cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString();
 
ASPX Code for Grid View
<h2>Account Summary</h2><br />
  
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" >
            <Columns>
                <asp:TemplateField >
                   
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text="Total"></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField   ><%--HeaderText="Deposits"--%>
                    <ItemTemplate>
                       
                    
                    </ItemTemplate>
                    <HeaderTemplate>
                        <a href="Deposits.aspx" >Deposits</a>
                    </HeaderTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
              
                <asp:TemplateField >
                    
                     <HeaderTemplate>
                        <a href="Expenses.aspx">Expenses</a>
                    </HeaderTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <ItemTemplate>
                  
                   
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField   >
                    <HeaderTemplate>
                        <a href="Income.aspx">Income</a>
                    </HeaderTemplate>
                  
                 
                    <ItemStyle HorizontalAlign="Center" />
                    <ItemTemplate>
                   <%-- <%# Eval("Course") %>--%>
                    
                    </ItemTemplate>
                </asp:TemplateField>
              
            </Columns>
        </asp:GridView>
 
Plz solve this.

View 4 Replies View Related







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