Can a Sql Server SP have variable number of arguments??? If yes, can somebody point me to a resource demonstrating that??? I need to send variable number of arguments to some of my SPs & iterate them with either foreach or traditional for loop. Any support would be greatly appreciated.
If variable number of arguments are not feasible, then can I pass them as an array to the SP (again a Sample code would be desirable)???
I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?
A simple illustation of this is as follows
A statement with two clauses :-
Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'
The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back
Is there any way to have variable length arguments for stored procedures - kinda like how there is in C/C++?
For example, if I wanted to send 2 arguments at one time, and then at another time send 7 to the same procedure, is that possible?
Hi,
I have a repeater control which I populate with search results from SQL Server.
But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?
I'm using a stored procedure with parameters:
input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords
Am using a temporary table to store all records before Select-ing those required for the particular page.
If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.
Am hoping that stored procedures can cope with an unknown number of parameters.
I am fairly new to MSSQL. Looking for a answer to a simple question.
I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.
The structure of this table is something like
PKID
customerID
email address
.....
customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...
Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?
Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.
Any other ways to do this? Thanks
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005
Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End
oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">"@CatId"</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;
gdvCategories.DataBind(); <<--- Error occured here
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies View RelatedHello. I am pretty new to SQL Stored Procedure, and ran into a problem. I am guessing there is a simple solution to this, as I am self-teaching myself this stuff.
I currently have a procedure that creates a new table, by reading in columns from a variety of Linked Servers (ODBC connection to to Sybase databases)
I will want to generalize a procedure by setting as arguments the ODBC connection name and the name of the new table that will be created ("CREATE TABLE") so I can pass these in.
As a test of this idea, I created this as a simple procedure, but I get a syntax error when I try to Check Syntax this. Do I have to do something else when I reference the string "argu1" to specify a new table name, or an OPEN QUERY Linked table name? Thanks and Happy New Year!
CREATE PROCEDURE gis.pr_PARCEL_TEST( @argu1 char(25)) AS
CREATE TABLE @argu1
GO
Hello,
When I try to call a stored procedure I get an SQL-exception reading "Procedure or function insert_member has too many arguments specified."
As far as I can see the number of parameters in the application match the number of arguments in the stored procedure. I supply 10 parameters in the application and 10 in the procedure. Is the location of the declaration of the argument cgpID a problem?
ALTER PROCEDURE [dbo].[insert_member]
(
@mbrFirstName nvarchar(15),
@mbrLastName nvarchar(15),
@mbrStreetAddress nvarchar(15),
@mbrPostalAddress nvarchar(15),
@mbrTelephoneHome nvarchar(15),
@mbrTelephoneJob nvarchar(15),
@mbrEmail nvarchar(15),
@mbrActivityGroupID int,
@mbrPaymentMethod int
)
AS
INSERT INTO dbo.member(mbrFirstName, mbrLastName, mbrStreetAddress, mbrPostalAddress, mbrTelephoneHome, mbrTelephoneJob, mbrEmail)
VALUES (@mbrFirstName, @mbrLastName, @mbrStreetAddress, @mbrPostalAddress, @mbrTelephoneHome, @mbrTelephoneJob, @mbrEmail)
declare @cgpID int
set @cgpID = (select cgpID from currentgroup where cgpActivityGroupID = @cgpActivityGroupID)
INSERT INTO member_currentgroup
(mbrID, cgpID, mcgPaymentMethod)
values
(@@identity, @cgpID, @mcgPaymentMethod)
I have the following stored procedure
ALTER PROCEDURE dbo.TextEntered
@searchString varchar(30)
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN
doesn't work!!!!
I would like to produce the following where s is @searchString:
Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME
Any ideas how I might acomplish this I have tried almost everything !
Hi,
We have an application written in ASP that calls a MS-SQL stored procedure and passes several parameters. Once in a while, we get this error, but most of the time it works. We can also run this in SQL query analyzer without ever a problem. We've looked at the obvious and found nothing wrong.
Please help! Any suggestions are appreciated.
(I posted this in ASP discussion board but no one replied)
Colleen
--------------------------------------------------------------------------------
|
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
View 1 Replies View RelatedI have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
Function GetCategoryName(ByVal ID As Integer) As String sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3 sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
Help I have not got much more hair to loose
Thanks Steve
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
I have received a change request for a project i am working on which im unsure if possible or not.
at the minute i have a very simple sp that simply selects data from a table by passing in one parameter
CREATE PROCEDURE PHAR_SelectGrade
@int_ID int
AS
SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
FROM tbl8Grade
WHERE Obsolete=0
AND GradeID = @int_ID
ORDER BY Grade
the user now wants the option to select more than one grade type (there are 100's of different grades)
so they might decide they want to see details for 2 grades, 22 grades etc. these would be selected and stored in an array (via an asp.net project) and then the stored procedure is called from within a web service
my question is how would i pass this array into the stored procedure??
i am assuming i would need to do something as follows for sp syntax, but im stumped on how i pass my array of values into the sp from the webservice and then for the sp to read the array in SQL??
CREATE PROCEDURE PHAR_SelectGrade
@myArray <what datatype?>
AS
SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
FROM tbl8Grade
WHERE Obsolete=0
AND GradeID IN (@myArray)
ORDER BY Grade
any ideas are greatly appreciated. or maybe its just not possible?!?
Cheers,
Craig
Hi,
we can use 'sp_executesql' to execute any statemens. I have made a search and people, seems, need the dynamic sql only to process some table/cloumn unknown in advance. My idea is that the dynamic SQL feature is ideal for passing blocks of code (aka delegates). Particularily, you may require to execute different procedures under some acquired locks. A procedure would acquire the locks, execute the code and release the locks. The problem is, however, that I cannot find the specification for the variable length parameters. It seems not feasible for SPs. Nevertheless, the 'sp_executesql itself does accept the variable number of parameters. How? Can we look at the defenition?
Can anyone tell me why I would be getting this error on the following query?
Wrong number of arguments used with function in query expression 'ISNULL((SELECT Sum(Game_Schedule.Score) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID),0) + ISNULL((SELECT Sum(Game_Schedule.Opp_Score) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID),0)'.
Im currently using Access 2000 which is connected to a SQL database. I have a query (Or View) that runs every month. The problem i had was i had to manually make some changes every month to get to the data i needed so i changed this View into a Function with a parameter so i can input the detail(s) i need and it will run correctly - this works so far but the original View was also used within other queries that were created. Now the problem i have now is some of the other views that are now connected with the newly created Function comes up with the error:
ADO error: an insufficient number of arguments were supplied for the procedure or function Name_Of_Function.
I called the newly created function the exact name as the original view to ensure i had no problems. Any idea of whats happening here and how to resolve?
Thanks
Hi all
I have select statement which is using Function and I am passing arguments to the query
select * from funactin_name(@a,@b,@c)
It is giving an error saying is that "An insufficient number of arguments to function..."
some body help how to find out required arguments to the function by using QA.
Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
Hello, I want to get data from datatable as below. I am getting error:
BC30516: Overload resolution failed because no accessible 'New' accepts this number of arguments. I did not understand what is wrong. Because everything is same as msdn library.
my codebehind is:
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClientPartial Class Default2 Inherits System.Web.UI.Page
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=Database;Initial Catalog=otel;Integrated Security=True;Pooling=False"
End FunctionProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim connectionString As String = _
GetConnectionString()
' Create a SqlConnection to the database.Using connection As SqlConnection = New SqlConnection(connectionString)
' Create a SqlDataAdapter for the Suppliers table.Dim mailsAdapter As SqlDataAdapter = _ New SqlDataAdapter()
' A table mapping names the DataTable.mailsAdapter.TableMappings.Add("Table", "Pages")
connection.Open()Dim PagesCommand As SqlCommand = New SqlCommand( _"SELECT * FROM Pages", _
connection)
PagesCommand.CommandType = CommandType.Text
' Set the SqlDataAdapter's SelectCommand.
mailsAdapter.SelectCommand = PagesCommand
' Fill the DataSet.
Dim dataSet1 As Dataset = New Dataset("Pages") 'ERROR MESSAGE HERE...........................................mailsAdapter.Fill(dataSet1)
connection.Close()LblPageName.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(1))
TxtPageTitle.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(2))TxtPageSummary.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(3))
Rte1.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(4))TxtPageimgUrl.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(5))
End Using
End Sub
End Class
hi how can I set my variable value with a query??? I am about to make a procedure to get a comma seperatet array and insert into the table. if I omit ID (primary key) and set it as Identity every thing is ok but I don't want it to be Identity. so I have to find the max of ID and then add it to the first of my comma seperated values. the problem is I have a table name as nvarchar in the parameters. so I have to first make a query as string and then execute it. anyway I need to get the value of the query string and assingn @ID variable to it's return value. how can I do it? so just suppose that I have @Values, @TableName (values are in the right order) then I want to find max(ID)+1 in @TableName (something like "select max(id)+1 from @TableName" I know that it will not work. so I make a string and execute it ) and add it to the first of my @Values then my problem will solve thank you in advance regards
View 3 Replies View RelatedI am building a stored procedure to get a list of rows, and I want to limit the rows returned to a variable. However, i keep getting a syntax error.
SQL:
SELECT TOP @TopArticles ArticleTable.*, ArticleSubSectionTable.SubSectionID AS SSID, ArticleTable.ArticleDate AS EXPR1
FROM ArticleTable
ERROR
Error 170: Line 28: Incorrect Syntax near '@TopArticles'
Any help would be appreciated,
PK
Hello experts!
I have a problem. I am trying to get a value from stored procedure A into a variable in storded procedure B, kind of like this:
Note: don't be fooled by the simplicity of these samples. There is no other way for me to solve this problem, but to get the value from sp_A into a variable in sp_B, since sp_A is in a database out of my control and i have no clue what happens therein...
CREATE PROCEDURE sp_A
AS
SELECT 'Hello world!'
RETURN
GO
CREATE PROCEDURE sp_B
AS
DECLARE @Value nvarchar(50)
SET @Value = --** Here i want to get the value from sp_A
SELECT @Value
GO
Thanks!
/Murti
Hi everybody, i have a problem and i hope anyone can help me.
I write a sp thayt include some variables. One of it is nvarchar(max) (because i think needed value is too big). Unfortunately, that variable is really too big (about 1000000 chars). It is out of bound of nvarchar(max). How can i do??
Please help me because i am working in it and it is too late now.
Thanks.
HelloI am a newbie to this, so I would appreciate any help, I am strugglingto get this to workCREATE PROCEDURE [dbo].[sp_test]@strfinalint as varchar(1000),@startdate as datetime@enddate as datetimeasdeclare @insertcmd as varchar(2000)declare @startdate as datetimedeclare @enddate as datetimeset @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:MyFolder' + @strfinalint + ';'',''SELECT * FROM [Sheet1$]'') Select * from tbltest WHERE S_DateBetween' + @startdate + 'AND' + @enddateEXEC (@insertcmd)GOIt was working with the commandset @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:MyFolder' + @strfinalint + ';'',''SELECT * FROM [Sheet1$]'') Select * from tbltest'But I am struggling to include the WHERE part of it, I seem to haveproblems making the variables work in this with appropriate quotationmarks !!ThanksSunny
View 3 Replies View Relatedhi
how can I set my variable value with a query???
I am about to make a procedure to get a comma seperatet array and insert into the table. if I omit ID (primary key) and set it as Identity every thing is ok but I don't want it to be Identity. so I have to find the max of ID and then add it to the first of my comma seperated values. the problem is I have a table name as nvarchar in the parameters. so I have to first make a query as string and then execute it.
anyway I need to get the value of the query string and assingn @ID variable to it's return value. how can I do it?
so just suppose that I have @Values, @TableName (values are in the right order) then I want to find max(ID)+1 in @TableName (something like "select max(id)+1 from @TableName" I know that it will not work. so I make a string and execute it ) and add it to the first of my @Values then my problem will solve
thank you in advance
regards
i have written procedure like belowALTER PROCEDURE GetDetails
(@vpid int,
@vpname varchar(30) OUTPUT,
@vprice int OUTPUT
)
ASselect @vpname=productname,@vprice=price from fastfood where prod_id=@vpid
RETURN
and codeSqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;SqlCommand cmd = new SqlCommand("GetDetails",con);cmd.CommandType = CommandType.StoredProcedure;
con.Open();
Hashtable items=new Hashtable();items = (Hashtable)Session["basketdata"];string name,price;
foreach(int s in items.Keys)
{
SqlParameter p1 = new SqlParameter("@vpid", SqlDbType.Int, 10);p1.Direction = ParameterDirection.Input;
p1.Value = s;
cmd.Parameters.Add(p1);
//cmd.Parameters.Add("@vpid", SqlDbType.Int, 10).Value=s;cmd.Parameters.Add("@vpname", SqlDbType.Char, 30).Direction=ParameterDirection.Output; cmd.Parameters.Add("@vprice", SqlDbType.Int, 10).Direction=ParameterDirection.Output;
cmd.ExecuteNonQuery();
name = cmd.Parameters["@vpname"].Value.ToString();price = cmd.Parameters["@vprice"].Value.ToString();
%>
}
showing too many arguments to procedure
Hi to all,
How can I Pass different number of parameters to a Stored Procedure?
In my Requirement,
Some times i want to pass 2 parameters only,
In some cases i want to pass 6 parameters.
How can i do this?
Please give me a solution.
Thanx in advance...
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
Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
View 8 Replies View RelatedI am trying to create a stored procedure that is sent a column name and orders by the column name. conn = New SqlConnection(SQLserver)SQL = New SqlCommand("SearchECN", conn)
SQL.CommandType = CommandType.StoredProcedure
SQL.Parameters.Add("@Search", SqlDbType.Variant)SQL.Parameters.Add("@Sort", SqlDbType.Variant)
SQL.Parameters(0).Value = Search.Text
SQL.Parameters(1).Value = "ECN.ECN"
SQL.Connection.Open()
GVECN.DataSource = SQL.ExecuteReader()
GVECN.DataBind()
SQL.Connection.Close()
SQL.Connection.Dispose()
Stored Procedure
@Search NVARCHAR(MAX),
@Sort NVARCHAR(MAX)
SELECT ECN.ECN, ECN.A, ECN.B FROM ECN WHERE ECN.ECN LIKE @Search OR ECN.A LIKE @Search ORDER BY @Sort
I get the following error
Msg 1008, Level 16, State 1, Line 10
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Any Ideas
hi everybody,
I have two stored procedures lets say A and B and a variable inside stored procedure A lets say @BReturn, now, what is the syntax to execute B inside A and to store its returned value in @BReturn? any help is greatly appriciated.
devmetz.