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