Using Table Name Stored In A Scalar Variable In Stored Procedure Problem
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
ADVERTISEMENT
Oct 15, 2014
I have created stored procedure to find out first word of the keyword. I am getting error below on execution:
"Must declare the scalar variable "@SubjectBeginning"."
View 9 Replies
View Related
Dec 12, 2007
Hi People, i'm having some trouble with a stored proceddure used for an updat from a Formview.
Error message i'm getting is Must declare the scalar variable "@CategoryID".
I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-)
Here is the whole store procedure.
ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update
@ID int,@CategoryID varchar(10),
@RegionID int,@CompanyName nvarchar(25),
@Email nvarchar(25),@PDFFront nvarchar(50),
@PDFBack nvarchar(50),@ThumbFront nvarchar(50),
@ThumbBack nvarchar(50),@Notes nvarchar(max)
AS
DECLARE @SQL varchar(1000)
SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '')
SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '')
SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '')
SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '')
SET @SQL = @SQL + ', ThumbBack = @ThumbBack'
SET @SQL = @SQL + ' WHERE (ID = @ID)'
Print '@SQL = ' + @SQLEXEC(@SQL)
RETURN
View 14 Replies
View Related
Jan 24, 2008
Hi all,
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".
=========================================================================================
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".
===================================================================
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Aug 24, 2007
I need to use a variable for a table name, pass it to a stored procedure that extracts data, and then store the result of the sp in another variable.
For example, earlier in the program I construct the table name using this statement:
@tablename = 'OC_TEMPLATE_' + @FORM. The resulting table name may by something like: OC_TEMPLATE_101
Now I need to use this table name in a statement like this:
SELECT ITEM FROM @TABLENAME WHERE DEX = @REC
If I use code like this, I can get a result, but I can't store it in a variable:
EXECUTE ('SELECT ITEM FROM ' + @TABLENAME + ' WHERE DEX = ' + @REC)
Running this give me a value of 309 in the results pane in Management Studio. 309 is the value I want, but I want to now store that in a variable called @ITEM.
I'm trying to run this as a stored procedure where I pass @tablename and @rec into the proc and I want to return @item as an output parameter with the value of 309 (in this case) in @item. I can't figure out how to configure the proc to give me the output parameter.
Any ideas?
T
View 6 Replies
View Related
Oct 31, 2007
I am working on an mailing list program and I am trying to create a preview option for the sender (to preview their message prior to sending)
I am new to .net and sql but I thought it might be best to do this with table variable inside a stored procedure.. I just do not know how to insert the data into the table variable and select that data at the same time, to return it back to a preview.aspx page.
Please forgive my ignorance..
here is my stored procedure..
ALTER PROCEDURE [dbo].[proc_MessagePreview]
@Subject varchar(150),
@EmailMessage varchar(max)
AS
BEGIN
DECLARE @MessagePreview Table(Subject varchar(150), EmailMessage varchar(max))
INSERT INTO @MessagePreview (Subject, EmailMessage)
VALUES (@Subject, @EmailMessage)
SELECT Subject, EmailMessage FROM @MessagePreview
END
any suggestion or help to point me in the right direction is appreciated, thanks!
View 3 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Jul 19, 2012
I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
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.
View 11 Replies
View Related
May 5, 2000
I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.
Select * from @Passedtablename. This won't work. Any insights.
View 1 Replies
View Related
Jul 29, 2015
What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?
Why can't the stored proc just have select statement instead of creating table variable?
View 3 Replies
View Related
Feb 8, 2008
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
View 4 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 7, 2015
I am creating a SP below and it keep saying must declare scalar variable @comments,I have already declared @comments varchar (255)
error---> (Msg 137, Level 15, State 2, Procedure CheckPatientComments, Line 26
Must declare the scalar variable "@commnets".
)
quote:
CREATE PROCEDURE [dbo].[CheckPatientComments] (@enc_id varchar(36) OUTPUT, @data_ind CHAR(1) OUTPUT)
AS
BEGIN
DECLARE
@comments varchar(255)
[code]....
View 2 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 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
Jul 23, 2005
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 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
Mar 20, 2008
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.
View 1 Replies
View Related
Jun 23, 2004
Hi all.
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
INSERT fo_categories (category_name, category_place)
VALUES(@category_name, @place)
RETURN
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 ?
View 2 Replies
View Related
Aug 2, 2004
Hi I am trying to achieve something like:
ALTER PROCEDURE dbo.GetShares
@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?
View 1 Replies
View Related
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
View Related
Feb 3, 2006
when passing a value to a stored procedure
How can you use that value within a Select Top <@Variable>
?
Below is the basic idea ..
CREATE Procedure SelectTop
(
@Number int
)
AS
SELECT TOP @Number *
FROM TableName
GO
View 3 Replies
View Related
Jan 31, 2006
Hello;
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?
Here's my code;
Function LieuBen()
MyCalendar = CurrTSCalendar
Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")
End Function
Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String
Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command
ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"
' Connect to the data source.
objConn.Open ConnectionString
' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn
objConn.MySProc MyCalendar, objRs
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing
End Function
View 1 Replies
View Related
Jan 19, 2004
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
View 4 Replies
View Related
