MS SQL Server :: How To Assign The SELECT Statement Output To A Local Variable?

How Do I Assign A String To A Parameter I'm Passing To A Select Statement?
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 Replies !

Retrieving To Local Variable From Select Top ...
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 Replies !

Select @Local-Variable From TestTable.!!!?
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 Replies !

How Do I Select A Column From Sp_spaceused Into A Local Variable?
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 Replies !

Select Into Local Variable Within A IF NOT EXIST Test?
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 Replies !

More Selecting Into Local Variable With Dynamic Select...
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 Replies !

Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such:

SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2'

Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as:

EXEC (@BuildDBPageUsed)

The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed?

*Thanks* for any help in this matter.
Brad

View Replies !

How To Use SP's Output In The SELECT Statement
hi guys!it's very very simple question for you mighty sql DBAs. but very hardfor a developer like me who is very very new to MS SQL.anyways the problem is i want to use one SPs out to in the SELectstatement. here is an example :select * from sp_tables tablename like 'syscolumns'please note that this is just an example. i'm using different SP but iwant to use in the same way.if anybody has anything to say. please write to me. i would be glade toread your repliesThanks,Lucky

View Replies !

Interesting Output In A Select Statement
Hi,

If I query like this, am getting the following Output.. Why is the part before '_ ' gets truncated and gets displayed as result ?

SELECT 10_to_100

_to_100
-----------
10

SELECT 25_from

_from
-----------
25

If anybody is familiar with the reason,pls share...

cheers !
ash

View Replies !

Select Statement To Output A Date
Hello Guys,

Please could you help me formulate a SELECT statement to output a date in the format (Month, Date Year), eg
October, 07 2007. Thanks.

View Replies !

Output Parameter Vs Select Statement
If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?

View Replies !

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
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 Replies !

Can You Have Multiple Output Parameters? Difference Between A Select Statement?
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?

Do I need just a select statement to do this? What is the difference between the output and select statements?

Thanks in advance.

View Replies !

Hiding Or Removing Column Output From Select Statement
I'm executing the following...

select COL1, min(COL2) from TABLE group by COL1

the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.

I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"

would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?

this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.

tia

a

View Replies !

Variable In A Select Statement
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 Replies !

Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter.

I want to do a Select statemant such as:

Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded --
Select name from pubs..sysobjects where xtype = 'u'

but I can't find the right way of using @DBName

View Replies !

Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter, for example-- @DBName varchar(30)

I want to do a Select statemant such as:

Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded --
Select name from pubs..sysobjects where xtype = 'u'

but I can't find the right way of using @DBName

Thanks,
Judith

View Replies !

Select Statement That Will Output Related Rows With Different Column Data Per Row?
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:

rowtype|   ID    | value
A          |   123 | alpha
B          | 123 | beta
C          | 123 | delta
A          |   124 | some val
B          |   124 | some val 2
C          |   124 | some val 3
etc...

where for each ID, I have 3 rows that are associated with it and with different corresponding values.

I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.

i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..

After my loop, I will just do a select * from #someTempTable

Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005

View Replies !

SQL Select Statement With A 'string' Variable?
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 Replies !

Using A Variable For Tablename In Select Statement?
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 Replies !

Variable For The Table Name In A SELECT Statement.
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 Replies !

Variable Not Holding Value For Select Statement
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 Replies !

Use A Variable Along With The FROM Clause In SELECT Statement
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 Replies !

How Do I Use A Variable To Specify The Column Name In A Select Statement?
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 Replies !

Put Select Statement In SSIS Variable
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 Replies !

How To Create A Select Statement With An Increasement Variable?
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 Replies !

Return Variable Name As Part Of Select Statement.
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 Replies !

Getting A Variable That Has A SELECT Statement To Return Results
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 Replies !

Storing Results Of Select Statement In @variable

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 Replies !

Unable To Create Variable Select Statement In For Each Loop
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 Replies !

How To Write A SELECT Statement And Store The Result In A Session Variable
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 Replies !

Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
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 Replies !

Output Assign To Parameter....from Execute Sql Task.
Hi all,

I'm trying to capture the OUTPUT from Execute Sql task...However when I run, the parameter didn't seem to capture the OUTPUT.

In my Sql Task, the parameter mapping:
Variable name: user::variable,
direction: OUTPUT,
Data Type: Varchar,
parameter name: 0,
parameter size: -1

connectiontype: OleDB
sourcetype: direct input
statement ELECT   columnx FROM table1 WHERE     (columnID=
                          (SELECT     MAX(columnID) ASID
                            FROM          table1 A)

I could be misunderstood on how Execute sql task work on Output.

thanks

View Replies !

Assign Variable Within SP
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 Replies !

Assign XML To Variable
How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?

I know that the following code works....

select * from tblUsers where userId = 1225 for XML raw

It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."

declare @strXml nvarchar(1000)

set @strXml = (select * from tblUsers where userId = 1225 for XML raw)

Ideas?

View Replies !

Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?

SET NOCOUNT ON

Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000

Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)

Set @Counter = 0

while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end

Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'

Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'

Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'

select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type

from @NumericBase

SET NOCOUNT OFF

View Replies !

How To Assign The @@IDENTITY To A Variable
Hi,

HOW can I assign the value of @@IDENTITY to the any variable in SQL SERVER .

Thanks,

View Replies !

Assign A Value To A Variable Without Using SQL Task
Greetings once again,

I am trying to achieve a seemingly simple task of assigning datetime value to a user variable at the point my package starts running. How can I do this without using a SQL Script Task? Should I be using a script task for this or is there a simpler way to achieving the same thing?

Thanks in advance.

View Replies !

Assign Null Value To Variable
I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro

The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Am i getting this error becasue the column has NULL value?. how can I resove this probelm?

View Replies !

How To Assign String Value To TEXT Output Parameter Of A Stored Procedure?
Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo

View Replies !

Assign Value To The Variable In Sql Server Procedure
hi friends..... i want to assign value to the nvarchar variable on sql server2005 procedure ...tell me how ? thanks and regards.samuel chandradoss . J

View Replies !

Using A Variable To Assign An IDENTITY SEED
I'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get errors so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.

View Replies !

How To Assign Query Results Into A Variable
Hello,

Could anyone help me please,

I need a select to add the results into a variable of type string

id(int)     name (varchar2)
1            a
2            b,c
3            d,e,f,g

tried wit the following:
DECLARE @variable1
SELECT @variable1 = name
FROM table

The problem with this sentece is that the only value assigned to @variable1 is the last row value, i.e.
@variable1 = "d,e,f,g"

What I need is to concatenate all values returned by the query, i.e.
@variable1 = "a,b,c,d,e,f,g"

Thank you very much in advance,

Marco

View Replies !