Wednesday, March 28, 2012
How to assign values to variables in a procedure in with select s
I have code like this
:
CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
OUTPUT,@.sex VARCHAR(1) OUTPUT
AS
SELECT NAME,SEX from table1 where .....
Now I want to assign the value of Name and sex from the select statement to
variables @.name and @.sex.
How do I do that. I just do not want to execute the select statement twice
as the selection criteria is big.
Thanks,
JSSELECT @.name = name, @.sex = sex
FROM table1
WHERE (something that guarantees exactly one row)
"JS" <JS@.discussions.microsoft.com> wrote in message
news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
> Hi,
> I have code like this
> :
> CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
> OUTPUT,@.sex VARCHAR(1) OUTPUT
> AS
> SELECT NAME,SEX from table1 where .....
> Now I want to assign the value of Name and sex from the select statement
> to
> variables @.name and @.sex.
> How do I do that. I just do not want to execute the select statement twice
> as the selection criteria is big.
> Thanks,
> JS
>sql
How to assign string value to TEXT output parameter of a stored procedure?
I am currently trying to assign some string to a TEXT output parameter
of 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
)
AS
INSERT INTO Table (blob_data, clob_data) VALUES \
(@.blob_data, @.clob_data);
GO
-- 8< --
My previous attempts include using the convert function to convert a
string into a TEXT data type:
SET @.clob_data = CONVERT(text, 'This is a test');
Unfortunately, this leads to the following error: "Error 409: The
assignment operator operation cannot take a text data type as an argument."
Is there any alternative available to make an assignment to a TEXT
output parameter?
Regards,
ThiloIs there a reason you can't just do a select on it?
Monday, March 26, 2012
How to Append Multiple Queries to a Temp Table?
Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?
Select * From TableA
UNION
Select * From TableB
<--output result to temporary table here-->
Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?
The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.
Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB
Thanks in advance.I think if you specify the SELECT * INTO in the top half of your union it ought to do the trick.
Yes this works...
USE NORTHWIND
GO
SELECT * INTO EMPLOYEES2
FROM EMPLOYEES
UNION ALL
SELECT * FROM EMPLOYEES
Points deducted for style though, never use SELECT * in production. Need a column list, do this...
SELECT COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
ORDER BY ORDINAL_POSITION|||Thanks Thrasymachus - that's exactly what I was looking for.
...and I'm sure you'll be pleased to hear the SELECT * was only for the example's in this thread. :D
Friday, March 9, 2012
How to add date and time to Flat File Connection Manager
Nice one:
Expression:
"FileNameBase_" + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()
Looks like this:
FileNameBase_2006-01-03_08:55:28
Place is on the Connection String property of the Flat File Connection Manager.
HTH,
K
|||
I have the following expression attached to the ConnectionString property of a File Connection Manager:
@.[System::PackageName] + (DT_STR, 4, 1252) DATEPART( "yyyy", @.[System::StartTime] ) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART( "mm", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "dd", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "hh", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "mi", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "ss", @.[System::StartTime] ), 2) + ".log"
Which looks horrible, I know, but evaluates to something alot more user-friendly:
PackageName20060103084130.log
You can choose to make this as difficult or simple as you like. For example
@.[System::PackageName] + (DT_STR, 30, 1252) @.[System::StartTime]
evaluates to:
PackageName03/01/2006 08:41:30
Hope that helps!!
-Jamie
|||Thanks a lot! this gives me a real good start to working more with expressions in SSIS|||
one more question. I tried putting it at the end of my string but it doesn't like it because it is malformed:
C:\Documents and Settings\sss\Desktop\output.txt + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()
|||Gotta escape the '\' character and gotta be in quotes... :)
"C:\\Documents and Settings\\sss\\Desktop\\output.txt" + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()
Friday, February 24, 2012
How to add a calculated field in query out-put?
Dear All,
I wanted include a calculated field (column) in my query output. how do i go about that?
Please help me.
SELECT amtcol1, amtcol2, amtcol1 + amtcol2 as 'Total'
or
SELECT amtcol1, amtcol2, 'Total' = amtcol1 + amtcol2