Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts

Wednesday, March 28, 2012

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 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 records of one table to another?

Hi I have a table with the following structure:

Table1
---
Dept
Filed1
Filed2
Field3
Field4
Field5

I have another table with the following structure
(Basically this table will contain a subset of coloumns of Table1)

Table2
---
Dept
Field1
Field2

Now using a query I would like see all the records with all coloumns in Table1 plus all the records in Table2 appended

i.e
if Table1 row is

IT F1 F2 F3 F4 F5

and if Table2 row is

IT F11 F22
Sales F12 F23

I would like to see a result set with the following structure

Resultset

IT F1 F2 F3 F4 F5
IT F11 F22 NULL NULL NULL
Sales F12 F23 NULL NULL NULL

Can some body explain me how to do this with a query. I tried using union but it requires identical coloumns on both ends( Ofcourse, we can acheive this by having Field3,Field4 and Field5 as blank columns in Table 2 but I don't wanna do that as my original tables are too huge to handle this).

Any input is appreciated.

Thanks,
SaiOne ugly way to do it is:

select column1, column2, column3, column4, column5, column6 from table1
union
select column1, column2, column3, null, null, null from table2

Pain in the butt to do if you have a lot of columns but it will get you the rs back that you want.

Good luck.

Friday, February 24, 2012

How to add 3 days to the existing date

Bottom is my table structure. please some one can give me the sql code to add 3 days to the existing date.

SemesterID int
SemesterLongID varchar
SemesterIdentifier char
SemesterIdentifierName varchar
SemesterName varchar
SemesterStart datetime
SemesterEnd datetime
SemesterNameAlt varchar
EnrollmentDeadline datetime
RegistrationStart datetime
RegistrationEnd datetime
LengthInWeeks varchar
BulletinStartDate datetime
BulletinEndDate datetime
ClassroomStartDate datetime
ClassroomEndDate datetime
FacClassStartDate datetime
FacClassEndDate datetime
PlanningStart datetime
PlanningEnd datetime
GoToTeach datetime
TOButAppears datetime
TOButDisappears datetime
Signature datetime
TOButDue datetime
BookListDue datetime
ProfAccess datetime
ExamDue datetime
SCGDue datetime
GradesDue datetime
ExtGradesDue datetime
TOExt datetime
SessionPlanning datetime
TODue datetime
SCGStart datetime
TOExpire datetime
SRPTOCDue datetime
SRPCopyDue datetime
SRPCCCFormDue datetime
TOID int
SemesterIdentifierID int
Code varchar
TitleIVDescription varchar
CreatedBy varchar
ModifiedBy varchar
DateCreated datetime
DateModified datetime

Check out books online for "Datediff" and "Dateadd" functions.