I've been curious why some variables don't need to be declared with a value type in a stored procedure. Please see the example below in the bolded area. The stored procedure works fine - nothing is wrong with it, but I just wanted an explanation on why and when is a value type not needed for a variable.
CREATE PROCEDURE [DBO].[EmailRpt] ( @TagDest char(2) = NULL, @DateWanted smalldatetime = NULL, @CustName varchar(64) = NULL, @AcctID AcctId = NULL, @ContactPhn phone = NULL, @CustAddr address = NULL, @City city, @CrossSt varchar(50) , @Access varchar(50) = NULL, @Activity tinyint, ) AS DECLARE @String as varchar(2000), @Header as varchar(200), @MailBox as varchar(50), @ActivityName as Name, @Return as int, @UserName as Name
Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below :
declare @pYear_Internal as NVarchar(100) set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE] WITH MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE] SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal)
[code]....
While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect. Â It looks like it doesn't recognize DECLARE keyword as it does with SQL queries. Â I just want a query that runs directly against the server.Â
i have used @ symbol for declaring local variables in stored procedure.... The symbol @@ is there....where to use and what is the purpose of using that symbol...
Hi, I'm try to get the query second column value when it is assinged to a varchar variable. Ex:In SP below is statement I wrote SET @Values = (SELECT COL1,COL2 FROM TABLE) Question 1: How to access the COL2 value from @Value2? Question 2: How to access the other row values if above returns more than a row? Please send me the solution as soon as possible. Thanks-Vikash/Bala
Can someone say how to declare a record like variable in MSSQL-2000 Like: mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script.
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom )
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
I am reviewing some code we have inherited (riddled with multiple nested cursors) and in the process of re-writing some of the code. I came across this and it has me puzzled.
As I understand it, if you declare a variable and then try to re-declare a variable of the same name an error is generated. If I do this inside a While loop this does not seem to be the case. What ever is assigned is kept and just added to (in the case of a table variable)
I understand things are in scope for the batch currently running but I would expect an error to return (example 1 and 2)
--Table var declaration in loop SET NOCOUNT ON DECLARE @looper INT = 0 WHILE @looper <= 10 BEGIN DECLARE @ATable TABLE ( somenumber INT )
here is the code segment below; ... DECLARE find_dates CURSOR FOR SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' EXEC (@SQL)
but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.
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.
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.
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.
Simple example:    declare @tTable(col1 int)    insert into @tTable(col1) values (1)    select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
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...
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
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
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
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
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'
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.
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.
1. I have a stored procedure, the procedure need create new category for forums, each category have their own place (what going first , what going second etc...), it's importent for me that all categories will be in property for them place, so i must read next place from table, and if not then i set it = 0 . But it's seems not working, i getting error. Can somebody tell me please what wrong ?
ALTER PROCEDURE dbo.CreateCategory ( @category_name varchar(100) )
AS declare @place int
/* setting place to zero */ SET @place = 0
/* trying to get last place and set value into @place */ SET @place = (Select max(category_place) FROM fo_categories)
/* if got nothing from table then setting value of first category */ IF (@place = 0) set @place = 1
Running dbo."CreateCategory" ( @category_name = Public forums ). Cannot insert the value NULL into column 'category_place', table 'mg_forum.dbo.fo_categories'; column does not allow nulls. INSERT fails.
2. I also would ask what SET NOCOUNT ON/OFF mean and for what i can use it ?
@SortValue varChar(30)= SHARE_DESCRIPTION as SELECT SHARES.SHARE_DESCRIPTION, SHARES.SHARE_SYMBOL, SECTORS.SECTOR_NAME FROM SHARES INNER JOIN SECTORS ON SHARES.SECTOR_ID = SECTORS.SECTOR_ID ORDER BY @SortValue but it does not seem to be possible to use variable after order by is there any way to achieve something with sorting by variable?
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.
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
Hello again folks, I've come crying for some more help :(
I have an sql query that runs fairly well and I'd like to make it a stored procedure.
The only hickup here is that I need to be able to send a field name in the select statement as a variable.
SQL Server does not allow this and I'm at a loss for the best way to handle it.
Thx for any help :)
CREATE PROCEDURE spReturnandScoring (@varKeyField as varchar(100),@varRegionID as varchar(10)) AS Select @varKeyField,count(*) 'SurveysSent',Sum(SD.return_status) 'SurveysReturned',avg(alScoring.Score) 'SurveyScore' From tblSurveyData SD left join (Select Return_Key,cast(sum(response) as numeric)/cast(count(*) as numeric) as 'Score' from tblResponses RE group by return_key) alScoring on SD.objid = alScoring.Return_Key Where Region_ID=@varRegionID Group By @varKeyField Order By @varKeyField GO
In the code below, the statement 'Print @Sites' prints nothing, eventhough the cursor contains 4 records, and 'Print @Site' prints theappropriate values. Can anyone see my mistake? I am attempting toprint a delimited string of the values referred to by @Sites.Thanks.Dan FishermanDECLARE SiteCursor CURSORGLOBALSCROLLSTATICFOR SELECT OfficeName FROM ClientOffices WHERE ClientID=12 ORDER BYOfficeNameOPEN SiteCursorDECLARE @Sites varchar(1000)DECLARE @Site varchar(100)FETCH NEXT FROM SiteCursor INTO @SiteWHILE @@FETCH_STATUS=0BEGINprint @SiteSET @Sites = @Sites + ', ' + @SiteFETCH NEXT FROM SiteCursor INTO @SiteENDPRINT @SitesCLOSE SiteCursorDEALLOCATE SiteCursorGO
Can someone post a working stored procedure with output variable thatworks on northwind db or explain to me what I am missing. I've triedever tying but it always returns @outvariable parameter not supplied.I've read everything but must be missing something. Here is an sampleSP that I can't get to work._____________________________________CREATE PROCEDURE SampleProcedure@out smallint OUTPUTASSelect @out = count(*) from titlesGO_____________________________________This errors and returns-Server: Msg 201, Level 16, State 4, Procedure SampleProcedure, Line 0Procedure 'SampleProcedure' expects parameter '@out', which was notsupplied.What am I missing with this?Frustrated,Jeff
Hello Forum !I want to have the tablename "dbo.Enbxxxx" as an additional parameterfor a procedure like this:ALTER Procedure prcSucheUNR(@UNR int)Asset nocount onSELECT ABRUFNR,UNR,STICHTAG,Datum,InhaltINTO #temp FROM dbo.Enb WHERE UNR = @UNRFor some reason:@tablename varchar(11),and: INTO #temp FROM @tablename WHERE .... does not work. :-(I get the following syntax Error:Zeile 33: Falsche Syntax in der Nähe von '@tablename'.I'am sure it is possible but i don't know how.Greetings