How To Pass Table Name As Paramater In Stored Procedure
Feb 5, 2008
I am using SQL2005 and would like to know if it is possible to pass a table name as an input parameter in a stored procedure?? I am struggling to get it to work.
For example:
CREATE PROCEDURE dbo.StartBlock
@TableName varchar(30),
@minX float OUTPUT,
AS
BEGIN
SET NOCOUNT ON;
SET @minX = (SELECT min(X) as mX
FROM @TableName);
END
GO
The above does not work and give the following error
Must declare the table variable "@TableName"
Thanks
Christie
View 5 Replies
ADVERTISEMENT
May 22, 2000
make it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Any help is GREATLY appreciated. Thanks.
Robert
View 1 Replies
View Related
Jul 23, 2005
Ok,This sounds dangerous (and yes I know it is)But its in a controlled enviroment and I really need to know how to dothis.How can I pass a Subquery for an Exist or In clause as a paramaterSomething like thisCREATE procedure dbo.mytry@funk varchar(1000)asSelect * from Customers where exists(@funk)GOSo I would execute something like soexec mytry @funk='Select ID From Customers where ID < 100'Any Ideas, I have tried LOTS of things but I can actually get it towork.I need to use it conjunction with a 3rd party product that can onlyselect from a Stored Procedure, and I can only pass paramaters to theSP.Any suggestions ?ThanksChris
View 6 Replies
View Related
Dec 16, 2005
Hi all,Seems like a fundamental question to me but I dont have a definiteanswer for it, Gurus please enlighten me.I have a table 'Table1' whose structure changes dynamically based onsome configuration values from another table. This table is being usedby a program, It was initially used by this program which ran as asingle task (executing at only a specific interval) but now the programhas to be run mutiple times some coinciding with each othe - whichmeant that table structure will change as 2 programs are runningsimultaneously... and therefore I have decided to use seperate tablenames that each has a structure of its now.I use this table name 'Table1' in about 10-15 stored procedures andUDF'sto make the long story short: Since I will not know which table I willbe using in the program I want to pass the table name as an argument tothe SP and UDF's and then access this param in the'select's/updates/inserts' - but this doesn't work unless I use DynamicSQL.Is there any other way of passing table names as parameters and thenusing then in the procs?any ideas will be really helpful.adi
View 5 Replies
View Related
Jul 23, 2005
Dear Techies,I making one stored procedure, which does some operation based on aninterface hash (#) table ---- name #mydata.This stored has two section of code (seperated by parameter value 0and 1)But hash table #mydata (same name) number/name of columns changes asper call 0 or 1.e.g.when call for 0, ----> Pass 2 columns as company_cd and section_cd ininterface hash (#) table ---- name #mydata.when call for 1, ----> Pass 3 columns as Section_cd, line_cd andsubline_cd in interface hash (#) table ---- name #mydata.As a result, none of the case (0 or 1) is running properly, It givesproblem.When I execute procedure for 0 by passing #mydata with two columns---> it gives problem in 1 section codeAnd When I execute procedure for 1 by passing #mydata with threecolumns ---> it gives problem in 0 section codePlease suggest !!! If anybody have faced the same problem or have anyidea about this case.(I think passing hash table with 3 column as col1,col2,col3 can servethe purpose, but this may cause rework in my case, so looking foralternate solution)Thanks in Advance,T.S.Negi
View 1 Replies
View Related
Oct 19, 2006
Hey everyone I'm having trouble finding a way to pass a particular paramater, my main goal of this is to create a custom paging and sorting control for the DataList, but we wont worry about all the un-nessesarry code, here is what I am dealing with... Sub Page_Load()
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
Dim myDataReader As SqlDataReader
' Define connection object
Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
' Define query to retrieve main category values
Query = "SELECT [productMainCategory] FROM [User_Products] WHERE [productMainCategory] = @productMainCategory"
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection to database
Conn.Open()
' Create DataReader
myDataReader = SqlComm.ExecuteReader()
' Iterate through records and add to array list
While myDataReader.Read()
IDList.Add(myDataReader("productMainCategory"))
End While
' Close DataReader and connection objects
myDataReader.Close()
myDataReader = Nothing
Conn.Close()
Conn = Nothing
' If page has not been posted back, retrieve first page of records
If Not Page.IsPostBack Then
Paging()
End If
End Sub
The Entire Point to this bit of code is to open a connection to the database find the category productsMainCategory, count all the fields with the Name 3D Art --> and save that number into an Array List where I will be using it later on in my code......The big old problem here is that it counts every single field in the productsMainCategory, Columb.........I need to figure out how to pass it a parameter so that it only counts my 3D Art --> fields, I have tried using this bit of code but nothing SqlComm.Parameters("@productMainCategory").Value = "3D Art -->"Does anyone have any ideas of how I might go about donig this?Thank You..
View 4 Replies
View Related
Sep 9, 2014
I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:
ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as
Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table
Once I have created the Temp table following query I need to run
SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)
The above query might return null result or a date .
In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"
View 3 Replies
View Related
Apr 26, 2008
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies
View Related
Jun 21, 2007
Hi all,
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
Thanks for your time.
Pace
"Impossible is Nothing"
View 1 Replies
View Related
Jun 21, 2007
Hi all,
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
Thanks for your time.
Barry Andrew
View 3 Replies
View Related
Apr 11, 2008
All i want to do is add a parameter for Territory_code how do i do that, in a stored procedure that uses a memory table.
the yellow shows the two places i thought i should be able to include it. Any help will be greatly appreciated!! thanks!
I couldnt post the whole code, so i deleted alot , just need to know where to put the param!
Code Snippet
USE [RC_STAT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_RPT_Breeder_Sales]
AS (@Territory varchar(20)
BEGIN
SET NOCOUNT ON;
Declare @ReportingTbl TABLE (
Source_Id int,
Territory_Code varchar(20),
Territory_Description varchar (30),
Sort_Id int,
Column_Text varchar(20),
Subbrand_Key int,
SubBrand_Description varchar (30),
Period_1 Decimal(18,0),
Period_2 Decimal(18,0),
Period_3 Decimal(18,0),
Period_4 Decimal(18,0),
Period_5 Decimal(18,0),
Period_6 Decimal(18,0),
Period_7 Decimal(18,0),
Period_8 Decimal(18,0),
Period_9 Decimal(18,0),
Period_10 Decimal(18,0),
Period_11 Decimal(18,0),
Period_12 Decimal(18,0),
Period_13 Decimal(18,0),
YTD Decimal (18,0),
Total_Amount decimal (18,0))
------------------------------------------------------------------------------------------------------------------------------------------
----Distributor Load Section
------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO @ReportingTbl
(Source_Id,
Territory_Code,
Territory_Description,
Sort_Id,
Column_Text,
Subbrand_Key,
SubBrand_Description,
Period_1,
Period_2,
Period_3,
Period_4,
Period_5,
Period_6,
Period_7,
Period_8,
Period_9,
Period_10,
Period_11,
Period_12,
Period_13,
YTD,
Total_Amount)
SELECT
1 as Source_Id,
Tbv_Customer.Breeder_Territory_Code,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name,
CASE WHEN cusSales.Customer_Sales_Summary_Year = YEAR(GETDATE()) THEN 2 ELSE 1 END as Sort_Id,
cusSales.Customer_Sales_Summary_Year Column_Text,
cusSales.Sub_Brand_Id ,
Qry_Report_Level_Brand.Sub_Brand_Description,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_1,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_10,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13,
0 as YTD,
SUM (cusSales.Customer_Sales_Summary_Amount) As Total
FROM RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension
INNER JOIN Tbv_Customer ON RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code = Tbv_Customer.Breeder_Territory_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales
ON Tbv_Customer.Customer_Code = cusSales.Customer_Code
INNER JOIN Qry_Report_Level_Brand
ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id
WHERE
cusSales.Bill_Customer_Code NOT IN ('RNPROC','RNPROF')
AND cusSales.Sub_Brand_Id <> 65 AND cusSales.Report_Level_Id = 85
AND cusSales.Report_Level_Id = 85
AND cusSales.Consolidated_Sales_Tables_Id = 6
AND cusSales.Customer_Sales_Summary_Year >= YEAR(GETDATE()) - 1
GROUP BY
Tbv_Customer.Breeder_Territory_Code,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name,
cusSales.Customer_Sales_Summary_Year,
cusSales.Sub_Brand_Id ,
Qry_Report_Level_Brand.Sub_Brand_Description
SELECT
Source_Id,
Territory_Code,
Territory_Description,
Sort_Id,
Column_Text,
Subbrand_Key,
SubBrand_Description,
Period_1,
Period_2,
Period_3,
Period_4,
Period_5,
Period_6,
Period_7,
Period_8,
Period_9,
Period_10,
Period_11,
Period_12,
Period_13,
YTD,
Total_Amount
FROM @ReportingTbl
-- ORDER BY Source_Id, Sort_Id
where Territory_Code=@Territory
END
View 6 Replies
View Related
Jan 21, 2014
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
View 5 Replies
View Related
Feb 22, 2007
Hi,
i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3)
My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens:
comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details
Maybe is my stored procedure wrong?
Thanks
T.
Here is it:----------
ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN
SET @iLoopNumber = 1
SET @amountr
While (@iLoopNumber <= @amount)
BEGIN
INSERT INTO table(field1,...)
VALUES (....))
SET @iLoopNumber = @iLoopNumber +1
End
COMMIT TRAN
View 3 Replies
View Related
Aug 11, 2007
How do I pass values from my ASP.NET page code into my Stored Procedure, to become parameters to be used in my Stored Proc?
Much thanks
View 2 Replies
View Related
Sep 19, 2005
Hi I have an if clause in my code to add the final parameter value to send to the database.
If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID"))
End If
If the user is in the role, the error is triggered saying that @C_ID
is expected by the stored procedure. If i then change the value from 0
to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.
View 1 Replies
View Related
Nov 30, 2005
hi,i am passing a xml file name to the stored procedure. the SP parses the file. but it is giving the error' INVALID AT THE TOP LEVEL OF THE DOCUMENT 'I expect this because of + and - in the xml file bafore the parent tags.how can i do the parser to eliminate these.
View 1 Replies
View Related
Mar 9, 2006
How can I pass a parameter to a stored procedure using Visual Web Developer 2005? I have created a SQLDataSource that calls the SP.
Thanks
--R
View 1 Replies
View Related
May 7, 2001
I have a two dimensional array in Front end (As for example Array contains 20 ECode and EmployeeName). I have a Stored Proc. where i have written a statement for inserting theses value in a table. so how i will pass this array to SP. Pls. give exmp. for Front end and SP also.
View 3 Replies
View Related
May 2, 2006
Hello,I read an article on how to use Yahoos API to GeoCode addresses. Basedon the article I created a stored procedure that is used as follows:SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'Returns:Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountryPrecision Warning----------- ---------- ------------- ------------- ------------------------------ --------38.889538 -77.08461 ARLINGTON VA USPrecision Good No ErrorIt returns Latitude and Longitude and other information. Works great.In conjunction with Haversine formula, I can compute the distancebetween two locations if I know the Lat and Long of the two points.This can start to answer questions like "How many students do we havewithin a 10 mile radius of Location X?"(Marketing should go nuts over this :)My question is how can i use my data from a table and pass it to theSPGeocode via a select statement?The table I would use is:CREATE TABLE "dbo"."D_BI_Student"("STUDENT_ADDRESS1" VARCHAR(50) NULL,"STUDENT_ADDRESS2" VARCHAR(50) NULL,"STUDENT_CITY" VARCHAR(50) NULL,"STUDENT_STATE" VARCHAR(10) NULL,"STUDENT_ZIP" VARCHAR(10) NULL);This is so new to me, I am not even sure what to search.TIARob
View 4 Replies
View Related
Jan 9, 2007
Dear All,
I am using sql2000, I want to know whether in stored procedure we can pass
array. Or is there any other solution to pass array of records
Please Guide Me
thanks
View 3 Replies
View Related
Jun 18, 2007
Hello,
I have a VB.NET stored procedure as below:
Code Snippet
Partial Public Class StoredProcedures
Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer)
'here I perform update statement using "paramInOut" passed form calling code
.......
'then I return value to the calling code
paramInOut = 555
End Sub
End Class
Calling code specifies a parameter like this:
Code Snippet
Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int)
param.Direction = ParameterDirection.InputOutput
param.Value = 999
cmd.Parameters.Add(param)
When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ?
Any help would be appreciated.
Thanks a lot,Fly.
View 4 Replies
View Related
Feb 1, 2008
Hi,
I want to pass an xml file to stored procedure in SQL Server 2000 as a parameter.
Can we pass the xml file path as a parameter? if not then how can it be done.
I want to serialize data to XML and then pass it to a procedure to insert data.
Kindly help ASAP.
Thanks
View 4 Replies
View Related
May 16, 2008
When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".
Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();
SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs
if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}
I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
View 3 Replies
View Related
Apr 29, 2008
How can I pass a parameter to a stored Procedure from another stored procedure in SQL 2005?
Thnak you,
View 3 Replies
View Related
Apr 3, 2007
In the snippet below, ExecuteSqlString is a stored procedure that accepts one parameter. SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass. I can't seem to find the correct syntax to get it to work. The way it is show below, the error comes back about incorrect syntax near ')' . Is this doable?
<asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"
DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>"
SelectCommandType=StoredProcedure
SelectCommand="ExecuteSqlString">
<selectparameters>
<asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/>
</selectparameters>
</asp:SqlDataSource>
View 6 Replies
View Related
Jun 7, 2007
I am trying to inject dynamically generated text into a Sql2000 stored procedure. What am I doing wrong?A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1. The routine splits the entry and creates the below string.Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%'
I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @VisitorKeywords.
PROCEDURE dbo.KWsearchAS SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( @visitorKeywords ) AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name
I am wanting the resulting WHERE portion to be:
WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1)
Thank you
View 10 Replies
View Related
Oct 29, 2007
hi,
i searched a lot to find how to pass an orderBy parameter finally i used a case block in my code and it works now how can i add a second parameter for ascending and descending order(@sortAscOrDesc)
when i use it after the end of case statement i get error
here is my sp:CREATE PROCEDURE [userPhotos]
@userID int,@orderBy varchar(100)
ASSELECT ID,UserID,Photo,ALbumID,Title,views,date_added from userAlbumPic where userID=@userID and albumID=0 order by
case @orderBy
when 'date_added' then date_added
when 'views' then [views]
else date_added
end
GO
View 7 Replies
View Related
Dec 18, 2007
i am trying to pass a large XML file from VS2005 (web service layer) to stored procedure (SQL Server 2000)In my stored procedure, the input parameter takes as "nText" (which will be XML file)Question:While performing ExecuteNonQuery, i am getting request timeout i think this is coz of large XML file i am passing.can anyone plz tell me how to pass XML file to SP...it would be better if you can provide me with some codei am completely new to this XML file passing between web service and SP...... thanks a lot in advance.....
View 7 Replies
View Related
Feb 19, 2008
I have stored procedure that expects 2 input parameters (@UserID uniqidentifier and @TeamID int). My datasource is SQLDataSource. So i need to pass parameters to SP..When i add parameters ans execute i got an error "....stored procedure expects @TeamId parameter, which was not supplied" But i pass them. How should i pass parameters? Maybe the reason is some mismatching of parametrs. 1 Parameter [] param = new Parameter[2];
2
3 param[0] = new Parameter();
4 param[0].Name = "@TeamID";
5 param[0].Type = TypeCode.Int32;
6 param[0].Direction = ParameterDirection.Input;
7 param[0].DefaultValue = "1";
8
9 param[1] = new Parameter();
10 param[1].Name = "@UserID";
11 param[1].Direction = ParameterDirection.Input;
12 param[1].DefaultValue = "edf26fd8-d7cd-4b32-a18a-fc888cac63ef";
13 param[1].Type = TypeCode.String;
14
15 dataSource = new SqlDataSource();
16 dataSource.ID = "Source";
17 dataSource.ConnectionString = settings.ToString();
18
19 dataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
20 dataSource.DataSourceMode = SqlDataSourceMode.DataReader;
21 dataSource.SelectCommand = "dbo.GetAprfAssessmentTeamData";
22
23
24 dataSource.SelectParameters.Add(param[0]);
25 dataSource.SelectParameters.Add(param[1]);
26
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 7, 2008
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
View 4 Replies
View Related
Jun 5, 2008
Either method is in the “ASPX� file
This is a DataSource for a “DetailsView� which has on top of “DeleteCommand� an “InsertCommand� a “SelectCommand� and an “UpdateCommand�. It is related to a GridView and the “@DonationRecID� comes from this GridView.
Method 1. Using an SQL Query – this works fine <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="DELETE FROM [Donations] WHERE [DonationRecID] = @DonationRecID">
Method 2. – using a stored procedure – this bombs because I have no clue as to how to pass “@DonationRecID� to the stored procedure "Donations_Delete". <asp:SqlDataSource ID="donationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:FUND %>" DeleteCommand="Donations_Delete" DeleteCommandType="StoredProcedure"> How do I pass “@DonationRecID� to the "Donations_Delete" stored procedure?
Does anyone have an example of how I can do this in the “ASPX.CS� file instead.
View 3 Replies
View Related
May 11, 2005
sSQL = "spBPT_Fuel_Set_Status_Approved"
cmdDailyPrices.CommandText = sSQL
cmdDailyPrices.Parameters.Add("@user", "Philippe")
cmdDailyPrices.Parameters.Add("@verbose", "0")
cmdDailyPrices.Parameters.Add("@Day_1_add", rowBand1.Cells(DayParameters.AddFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_1_multiply", rowBand1.Cells(DayParameters.MultiplyFactor).Value)
cmdDailyPrices.Parameters.Add("@Day_2_add", "NULL")
cmdDailyPrices.Parameters.Add("@Day_2_multiply", "NULL")
For @Day_2_add and @Day_2_multiply parameters I want to pass the value as NULL not string "NULL"
could you please let me know how to do this?
Thanks
View 1 Replies
View Related
