SELECT Statement That Assign Value To Local Variable
Nov 12, 2013
I am trying to figure out a way to retrieve a field value and assign it to a local variable with out destroying the whole structure of my T-SQL statement.
Here is the code:
DECLARE @AVERAGE_WHOLESALE_PRICE VARCHAR(20)
DECLARE @ORDERBY VARCHAR(20)
SELECT TOP 1 @AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX,
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
[Code] ....
The error message is
Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
How to modify this statement?
View 8 Replies
ADVERTISEMENT
May 7, 2008
In my program i have function that will get one value from Database.
Here i want to assign the output of the sql query to a local variable.
Its like select emp_id into Num from emp where emp_roll=222;
here NUM is local variable which was declared in my program.
Is it correct.?
can anyone please guide me..?
View 7 Replies
View Related
Mar 30, 2008
Hi
I want to use a variable in a number of SQL statements as paramenter. But I am not sure how to assign the value I want to the variable. The value I want is the maximum week number from one of my tables. I know how to put this value into a new table (which is one field and One row), but I cannot see how to pass this value to my variable. Using the table in my SQL is not straight forward unfortunately, at least not for me.
View 1 Replies
View Related
Jun 7, 2007
Hi this is probably a very stupid question, but I still need to know.
How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?
View 3 Replies
View Related
Jan 31, 2008
I wish to dynamically assign Select Statement to SqlDataSource. Anyone has any idea?? Is it possiable at all?
I am coding using C# under Framwork 2.0
View 1 Replies
View Related
Jun 16, 2008
How can i assign select statement to a sqldatasource
I mean if the user clicks button 1
sqldatasource=-"select * from customers where status='ClOSED' "
or else if the user clicks button2
sqldatasource="Select * from Customers Where Status='OPEN' "
I want to bind the sqldatasource to a gridview finally .
Is this possible ?
I tried sqldatasource filters But am reallly confused
Any suggestions/solutions are appreciated
thankyou
View 8 Replies
View Related
Oct 30, 2006
Hello, I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product. I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here: http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:
WHERE (OrderStatus IN ((select value from dbo.fn_Split(@StatusList,','))) OR @StatusList IS NULL) but now I don't know how to assign the string value to the parameter, say to '@solution_id'.my current select statement which was generated by Visual Studio 2005 looks like this: SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @solution_ID) ...but this only pulls results for the first item checked in the checkboxlist.Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.
View 3 Replies
View Related
Oct 7, 2007
Hi all
Imagine we have a table as follow:
Create Table Test(
Pkey int not Null identity Primary Key,
Name varchar(20) not null,
Famil varchar(30) not null
)
now is there anyway to Run following Script:
declare @FLDName varchar(10)
set @FLDName='Name'
Select @FLDName from Test
in the other hand is there anyway to use Local variables in a SELECT statements as above?
to tell you the truth , what really motivated me to do this , was being able to declare a User-Defined-Function and passing some fields as an String and then use them in a SELECT statement or any other T-Sql codes!!!
Could anyone help me.?
Kind Regards.
View 1 Replies
View Related
Feb 29, 2008
Hi,
I€™ve got the following statement :
SELECT TOP(1) Date, Status FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC
I want to retrieve the status field to a variable, but I€™m not being able to do so€¦ I think its due to the TOP function€¦
I have tried those with no success:
SELECT @var2 = TOP(1) [Date], @var = [ Status] FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC
SELECT @var2 = [TOP(1) Date], @var = [ Status] FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC
Is there Any way I can do that ?
Thanks
View 1 Replies
View Related
Feb 10, 2004
Hi all,
I'm trying to be slick, but so far am just all wet *snicker*
I've got a table with rows that may or may not exist, and am trying to retrieve a column if an associate row DOES exist.
For argument's sake, my PIndex table looks like:
PID int
CreateDate smalldatetime
CloseID float
Here is my select logic that I thought would save an extra select to load my local variable if the row is actually in the table...
DECLARE @CloseID float
IF NOT EXISTS (SELECT @CloseID = CloseID
FROM PIndex
WHERE ((PID = '14') and
(CreateDate = '2004-02-06')))
SET @CloseID = 100
The SET afterwards is just to initialize the variable if it can't be had from an existing row in the table.
The trouble is, that it fails to compile with the following error:
>>>>> Line 3: Incorrect syntax near '='.
Any insights? My goal is to use a single select to load the value into my local variable if the associated row exists, or to set my local variable to 100 if it doesn't.
Thanks!
Paul
View 5 Replies
View Related
May 25, 2004
Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:
Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
DECLARE @SQLCmd varchar(256)
DECLARE @TargetDate smalldatetime
DECLARE @TLevel int
DECLARE @n int
SET @TargetDate = '2004-05-24'
SET @TLevel = 1
SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' +
CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' +
CONVERT(varchar(3), @TLevel) + '))'
exec (@SQLCmd)
if @n > 0
print 'yep'
else print 'nope'
and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@n'.
nope
Thoughts?
View 8 Replies
View Related
Jul 20, 2005
I want to look at the size of the current database, so I can create a newone if it gets too big (we are working around the 2gb MSDE limit for ourcustomers).I would like to do something like this:DECLARE @size INTEGERexecute BLOB0000.dbo.sp_spaceusedand make @size = the database_size column value that sp_spaceused returns.Any way to do this?Thanks.
View 6 Replies
View Related
Oct 4, 2006
I am trying to set a vaiable from a select statement
DECLARE @VALUE_KEEP NVARCHAR(120),
@COLUMN_NAME NVARCHAR(120)
SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')
SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)
PRINT @VALUE_KEEP
PRINT @COLUMN_NAME
RESULTS
-------------------------------------------------------------------------------------------
FirstName <-----------@VALUE_KEEP
FirstName <-----------@COLUMN_NAME
SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName
SELECT FirstName from Contacts returns: Brent
How do I make this select statement work using the @COLUMN_NAME variable?
Any help greatly appreciated!
View 2 Replies
View Related
Sep 6, 2006
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.
select @column_name from table
View 2 Replies
View Related
Apr 23, 2007
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20))
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ProjectNumber int
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SELECT MAX(@ProjectNumber)
FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','');
END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
View 3 Replies
View Related
Sep 19, 2007
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!
View 1 Replies
View Related
Apr 16, 2008
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
View 1 Replies
View Related
Dec 7, 2005
this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on ((( @UsersMaxID ))) but for some reason it will not work with the next select statement...
can someone make the pain go away and help me here..??
erik..
GOSET ANSI_NULLS ON GO
ALTER PROCEDURE AA
ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200)
SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik'
DECLARE @SQL NVARCHAR(4000) DECLARE @UserID INT DECLARE @UsersMaxID INT DECLARE @MaxID INT
declare @tempResult varchar (1000)
-------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY,
UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)
Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr
FROM USERS
WHERE ' + @GenericColumn +' = ''' + @GenericValue + ''''
EXEC sp_executesql @SQL
SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29
SELECT * FROM #UsersTempTable
==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE
View 1 Replies
View Related
Dec 28, 2004
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.
What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).
This is the code in my procedure..
DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list
DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt
select count(*) from @tab_name
This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.
Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.
Please help me to construct the select statement that is similiar to
x=<table name>
select * from x
where x is a variable and the table name gets substituted.
what is the syntax for it ?
View 7 Replies
View Related
Nov 10, 2006
How do I use a variable to specify the column name in a select statement?
declare @columnName <type>
set @columnName='ID'
select @columnName from Table1
View 8 Replies
View Related
Sep 23, 2006
Is it possible to add a variable in SSIS like
name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])
@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made
I know I'm doing something wrong with the data type because it's stores the whole select statement as a string
Help
Worf
View 8 Replies
View Related
Dec 19, 2003
Example:
Select icount + 1 as icount from table
or
Select counter() as icount from table
The above is wrong ...just a sample to show what I am trying to accomplish.
Is there a function in SQL statement? Thanks.
View 2 Replies
View Related
Mar 9, 2008
hey all,
I have the following query:
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS
BEGIN
DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)
DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)
SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name
HAVING article_id = @article_id
END
GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
Cheers
View 6 Replies
View Related
Feb 11, 2008
I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:
SET @value = select max(price) from product
but this does not work, can someone tell me how I would go
about in storing the results in a variable.
@value is declared as int
Thanks in advance,
Sharp_At_C
View 1 Replies
View Related
May 21, 2006
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)
@a = Select * from foo ---Obviously mine is much more robust
How do I execute @a to actually Select * from foo?
View 3 Replies
View Related
Apr 21, 2015
Everything about this query works except I'm trying to capture the @companyid (which is a variable) into a column in my table via my select statement.
My error is Invalid column name 'A113', etc. However it is the A113 I'm trying to insert into the first column of the table SAP_GLsummary
-- retrieves a list of gl balances from all companies
truncate table sap_glsummary
declare @companyID char(6)
declare c_company cursor for
select INTERID from dbo.GP_Interid
open c_company fetch next from c_company into @companyID
[Code] .....
View 4 Replies
View Related
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
Apr 24, 2007
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
View 5 Replies
View Related
Nov 6, 2007
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
View 1 Replies
View Related
Mar 28, 2000
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list
I have difficulty combining a select statement with a where clause stored in a variable inside a cursor
The users select the mail content and frequency of delivery and i deliver the mail
I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.
Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work
For example
DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3
I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary
How do I combine the select statement with the where clause?
Help me...I need help urgently
View 1 Replies
View Related
May 26, 2015
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;
View 11 Replies
View Related
Nov 23, 2006
I use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?The name of the column I want to retreive the value from is "UserID"Here's my SP so far:
ALTER PROCEDURE [dbo].[spUnregisterUser]
@UserCode int
AS
BEGIN
SET NOCOUNT ON;
declare @uid uniqueidentifier
--get userid
SELECT @uid=UserID FROM tblUserData WHERE UserCode=@UserCode
-- Delete user
UPDATE tblUserData SET IsDeleted='True' WHERE UserCode=@UserCode
END
View 1 Replies
View Related
Oct 6, 2015
How to make it workable code..
declare @i int=1
declare @numweek int=2
declare @a int=35
declare @b int=29
declare @Wkstr1 date,@Wkstr2 date
[Code] .....
View 7 Replies
View Related
