How To Input A DOS Variable Into A Stored Procedure
May 4, 2004
I am trying to Execute a Stored Procedure using Call OSQL from a .bat file, and passing a DOS variable to the Stored Procedure.
The DOS variable is established in the DOS command...
set SERVERJOB=JOBNAME
I have tried...
EXEC sp_procedure %SERVERJOB%
With this, I get an error...
sg 170, Level 15, State 1, Server ABCDEFGH, Line 20
Line 20: Incorrect syntax near '%'.
If I put the variable name in quotes on the EXEC statement above, the value used is the variable name, %SERVERJOB% itself rather than the value the variable was set to, which would be JOBNAME in the above example.
Any ideas??? Thanks!
View 2 Replies
ADVERTISEMENT
May 6, 2015
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
View 4 Replies
View Related
Sep 15, 2015
I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
So what I'm looking for is basically 2-fold.
View 5 Replies
View Related
Jul 9, 2001
Is there any way to use table variable as input to a function or stored procedure?
View 3 Replies
View Related
Feb 27, 2008
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.
View 5 Replies
View Related
Apr 25, 2006
Hi, I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.
View 2 Replies
View Related
Jun 13, 2006
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great:
Code:
Dim cmd As New SqlDataSource
cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
cmd.InsertParameters.Add("@firstName", txtFirstName.Text)
cmd.InsertParameters.Add("@lastName", txtLastName.Text)
cmd.InsertParameters.Add("@address1", txtAddress1.Text)
cmd.InsertParameters.Add("@address2", txtaddress2.Text)
cmd.InsertParameters.Add("@city", txtCity.Text)
cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value)
cmd.InsertParameters.Add("@zipCode", txtZipCode.Text)
cmd.InsertParameters.Add("@telephone", txtTelephone.Text)
cmd.InsertParameters.Add("@email", txtEmail.Text)
cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value)
cmd.InsertParameters.Add("@birthday", txtBirthday.Text)
cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@question", txtquestion.Text)
cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
cmd.InsertCommand = "sp_insertCustomer"
cmd.Insert()
Stored Procedure:
CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000)
AS
INSERT tblCustomer
(firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question)
Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question)
ERROR:
Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Source Error:
Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
View 5 Replies
View Related
Feb 9, 2006
Hi,
I want to convert a SQL query as shown below into a stored procedure:
select name
from namelist
where town in ('A','B','D')
If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.
Thanks.
View 5 Replies
View Related
Mar 9, 2008
I am trying to send XML as an input parameter for a stored procedure. I have seen many articles that do a good job of describing different variations but all the examples show the stored procedure only pulling one value (field) per record from the XML input. I need to pull 3 fields for each record.
Here is an example of the XML being passed:
<object>
<property @propID="14" @propType="4" @propValue="Blah blah text" />
<property @propID="217" @propType="2" @propValue="Some other text" />
</object>
I have a table like this in my database:
CREATE TABLE SCENE_PROPERTY_LINK (ID INT, OBJ_ID INT, PROPERTY_ID INT, PROPERTY_VALUE NTEXT)
and I want a stored procedure that will accept XML and update this table.
Here is what I am trying:
CREATE PROCEDURE sp_UpdateObject
@inValues XML
AS
BEGIN
--create a temporary table
DECLARE @props TABLE(PROPID INT, PROPTYPE INT, PROPVALUE NTEXT)
--And then insert the values from the @inValues XML argument into the temporary table
--I am sure the SELECT statement is VERY wrong
INSERT INTO @props(PROPID, PROPTYPE, PROPVALUE)
SELECT @inValues('@propID', INT), @inValues('@propType', INT), @inValues('@propValue', NTEXT)
FROM @inValues.nodes('/object/property')
--...and then I will use the temp table to update the DB table (SCENE_PROPERTY_LINK) for each record where SCENE_PROPERTY_LINK.PROPERTY_ID = @props.PROPID
AND @props.PROPTYPE != 6
END
I am sure it would be more efficient to update the DB table directly from the XML argument, without using the temporary table. But, I will settle for this solution using the temp table. I have done some work creating XML output from several stored procedures but, this is the first time I have been faced with consuming XML input in SQL.
I apologize for the long post.
Thanks in advance for any help you can provide.
View 4 Replies
View Related
Sep 11, 2007
I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :
Declare @XMLString XML
Set @XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >
</Company>'
Exec sproc_Insert_Company @XMLString
The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..
Its being generated because of the '&' being used in CompanyName.
In my sp i m using it as : .
Insert Into Company(
CompanyName,
Code,
)
Output Inserted.CompanyId Into @tbl
-- TurnOver,
-- NetIncome,
-- YrOfIncorporation,
SELECT
CompanyName = ParamValues.Item.value( '@CompanyName' , 'varchar(101)'),
Code = ParamValues.Item.value( '@Code' , 'varchar(6)'),
FROM @XMLString.nodes('Company') AS ParamValues(Item)
Its not only this but there are other special characters which create problem like '@' and many more...
How to resolve it??
plzz do help at the earliest...
View 6 Replies
View Related
Jan 10, 2008
Hi All,
I have a project which will be a tools to edit different tables.
Now I need a stored_procedure to select data from different table.
For example I have a table name "TableFields" which have "tableID","FieldName", "DataType"and so on columns.
It has the following records.
"1","EmployeeID","Varchar"
"1","FirstName","varchar"
"1","LastName","varchar"
"1", "EmployedDate","date"
It has the following records.
"2","AddressID","int"
"2","ApartNo","varchar"
"2", "Address","varchar"
Then I have table named "Employee" has columns "employeeID","FirstName","LastName","EmployedDate" which have the following data,
"001","Susan","Daka","1999-09-09",
"002","Lisa","Marzs","1999-08-08",
"003","David","Smith","2000-01-01",
I also have address table has columns "AddressID","ApartNo","Address" and has the following data
"1","1101","1208 Mornelle Crt, Toronto",
"2","1209","1940 Garden Drive, Toronto"
I need to create a stored procedure to select data from table "employee " or table "address" or even other tables according to information from "TableFields."
So the table's name can be know as a input parameter, but the fields name will be a list of values and it all depends on tables.
I want to use fields name as a long string separated by",", like I have input "EmployeeID, FirstName,LastName" as an input parameter. But I don't know how to split the string.
Second, I need to create a stored procedure to insert or update data into these dynamically table.
Can anyone help me?
Thanks in advance.
View 8 Replies
View Related
Feb 11, 2001
Could someone help me get this stored procedure to work? I want to give the stored procedure a long list of departments and have them added to a temp table. This only gets the first dept. in the temp table. I'm confused. Open to other suggestions, but want to use a 1col temp table to hold the depts.
After this is done, an SQL query is run using the temp table.
Input for test:
--csi_crystal_xxxx "pc9xp,pc8,pc7,pc6,pc6543,pc945678"
--select * from ##CrystalGetCosts
create procedure csi_crystal_xxxx
@DeptResp varchar(4000)
AS
SET NOCOUNT ON
DECLARE @SQL varchar(8000)
DECLARE @Dept varchar(10)
DECLARE @iLen int
DECLARE @iPtr int
DECLARE @iEnd int
If Exists (Select name, Type From [tempdb]..[sysobjects]
where name = '##CrystalGetCosts' And Type = 'U')
Drop table ##CrystalGetCosts
CREATE TABLE ##CrystalGetCosts (Dept_Resp_No varchar(10))
Set @iLen=Len(@DeptResp)
Set @iPtr = 1
While @iPtr < @iLen
BEGIN
SET @iEND = charindex(',',@DeptResp,@iPtr)
Set @Dept= Substring (@DeptResp,@iPtr,@iEnd-1)
INSERT INTO ##CrystalGetCosts Values (@Dept)
Set @iPtr = @iEnd + @iLen
END
View 1 Replies
View Related
Jun 18, 2008
Hi everyone,
Is that possible to passing a table as an input to Stored Procedure?
Thanks in advance
View 1 Replies
View Related
Jul 18, 2006
Hi,
i wanna know if i can use a vector (or an array) for input parameter to a Stored Procedure? Can anyone give me example or links?
thanks
View 1 Replies
View Related
Jan 23, 2008
I have a stored procedure that takes an input string parameter defined as @name nvarchar(24). The stored procedure takes this string and insert it into a table with the column also defined as nvarchar(24). When I execute this stored procedure with a string of more than 24 characters, the input string somehow get truncated and inserted successfully into the table without giving an error. Why is this the case?
If I simply execute an insert statement with a string longer than 24 characters, it will give me an error message. I tried enclosing the insert statement with a try-catch block in the stored procedure but I still can't trap any error.
Any suggestions?
View 10 Replies
View Related
Jun 28, 2004
if i send the string
2,3,4,5 to a stored procedure...
is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?
View 7 Replies
View Related
Dec 9, 2005
could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE. I receive my 3 column headers in my datagrid but the parameter isn't doing anything
STORED PROCEDURE
CREATE PROCEDURE USP_Machcusidsearch
@Machcusid nvarchar OUTPUTAS
SELECT dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDateFROM dbo.Machine INNER JOIN dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemIDwhere machcustomID = @MachcusidGO
VB.NET CODE
Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged
SqlDataAdapter2.Fill(DsMachcusidsearch1)
SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value
DGstatussearch.DataBind()
End Sub
End Class
View 4 Replies
View Related
Dec 17, 2013
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference'
BEGIN TRY
select data
from mytables
END TRY
However I'm not sure this is the most efficient way to handle this.
View 2 Replies
View Related
Jun 8, 2015
I have the following Query.
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.PURCHLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID'
[Code] .....
My tabel is HST_MASTER.Control.
I want to have this query in a stored procedure. What syntax stored procedure i need to make to fill my table.
View 1 Replies
View Related
Apr 7, 2008
my stored procedure is
create procedure t1 (@a int,@b int,@c int,@d int,@e int,@f int)
as
begin
select no,name,department from emp where a = @a and b =@b orc =@c or
d = @d or e = @e or f = @f
end
my problem is while executing i may get null values as input to the stored procedure .
how to validate them ? any ideas .
are there any arrays.
if c and d are null then my condition would be
where a = @a and b =@b or e = @e or f = @f
how many loops shall i have to write.
View 4 Replies
View Related
May 14, 2008
Hi,
I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date,
ex:
if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert
Uniq_Id, emp_ass_id, start_date end_date status_id
1099
1001
1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM 1
1100
1001
1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM 1
1101
1001
1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM 1
1102
1001
1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM 1
1103
1001
1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM 1
the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.
Thanks in advance.
View 9 Replies
View Related
Sep 4, 2007
Hi guys, is there any way to solve my problem as title ? Assuming my stored proc is written as below :
CREATE PROC TEST
@A VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...,5000'
AS
BEGIN
DECLARE @B nvarchar(MAX);
SET @B = 'SELECT * FROM C WHERE ID IN ( ' + @A + ')'
EXECUTE sp_executesql @B
END
GO
View 2 Replies
View Related
Mar 27, 2006
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
View 3 Replies
View Related
Feb 23, 2008
Hi all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?
Please help and advise.
Thanks in advance,
Scott Chang
View 7 Replies
View Related
Jan 31, 2007
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 Related
Oct 2, 2000
I 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
View 2 Replies
View Related
Nov 8, 2004
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
View 4 Replies
View Related
May 16, 2008
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.
View 2 Replies
View Related
Nov 7, 2007
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 Related
Jan 31, 2007
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 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
Jul 19, 2007
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 Related
Mar 5, 2008
I 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
View 10 Replies
View Related
