Tuesday, April 30, 2013

[SQL Server] Store proceure with multiple result sets

[SQL Server] Store proceure with multiple result sets

Store proceure with multiple result sets

Posted: 29 Apr 2013 11:54 PM PDT

Dear AllI have one procedure PROC1 wich resturn multiple result sets. No of coulmn and Datatype of the result set are not same.I want to access the result of PROC1 from another proceure PROC2. How do i do it?RegardsKrishna

Copy certain tables,stored procedures and views from one Database to another

Posted: 30 Apr 2013 04:05 AM PDT

Hello,I am new sorry, hope i can explain this right, I have an old database called database1, which now we have a new database called database2 (these are not the real names lol), database1 and database2 have the same table names, stored procedures etc, but database1 had foreign keys etc, what I want to know is there a way to copy over only certain database tables from one database to another, not copying the entire database but only the tables needed and restore them onto database2? is there a way for doing the same for certain stored procedures and/or views and not all only certain ones? I have SSIS but so far i can only see for data purposes...Thanks in advance

Random Number Generator

Posted: 30 Apr 2013 12:11 AM PDT

I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes with the only criteria being that each lane number cannot be within 5 of any lane within 3 spaces of it. So what I mean is if lane 4 has a number of 30 then lane 1-2-3 and lane 5-6-7 cannot be within 5 of lane 4's number. Hope this makes sense and any help would be sincerely appreciated....

Rollback in trigger

Posted: 30 Apr 2013 12:38 AM PDT

Dear AllI have read that if there is a rollback in the trigger then it rollback the transaction in the trigger plus the original dml statemt to which the trigger is attached.I tried this with folloing codedrop table agocreate table a( i int)gocreate trigger a_inserton afor insertasbegin declare @a as varchar(40) set @a = 'count of transaction' + CONVERT(varchar(10), @@TRANCOUNT) print @a rollbackendgoinsert into a (i) values (2)REsult of the above insert is as followscount of transaction1Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.why is it giving the error message?. How to avoid it?RegardsKrishna

Updated muliple rows into one column? Why is it hard? HELP

Posted: 29 Apr 2013 10:05 PM PDT

Trying to update a table using integer number in 3 columns and a signing texts name to results in the second table TEST2 It only updated that last update statament which was Volunteer. It should upadte with all the other columns from TEST1.I even try using update with subquery but I got a error to do with multipe values. And I got no idea how to do the[b] if else [/b]part of the sql if any other number come up.Thanks in advance!!Actual Results by using SQL Query below-[b]Event_Role[/b]VolunteerWanted Results-[b]Event_Role[/b]Speaker Speaker Speaker Speaker coordinatorcoordinatorcoordinatorcoordinatorVolunteerVolunteerVolunteerVolunteerOtherOtherOther[code="sql"]CREATE TABLE [dbo].[TEST1]( [Ispeaker] [int] NOT NULL, [Iscoordinator] [varchar](35) NULL, [Volunteer] [int] NULL,) ON [PRIMARY] GOInsert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);Insert into TEST1 (Ispeaker,Iscoordinator,Volunteer) VALUES (-1,-1, -1);GO--create empty column in table2Select CAST(NULL AS VARCHAR(20)) AS Event_Roleinto dbo.TEST2UPDATE [dbo].[TEST2] SET Event_Role = 'Speaker'FROM dbo.TEST1Where Ispeaker = -1 UPDATE [dbo].[TEST2] SET event_role = 'Coordinator' FROM dbo.TEST1WHERE IsCoordinator = -1UPDATE [dbo].[TEST2] SET event_role = 'Volunteer' SELECT ID FROM DBTEAM.azeez.Participants WHERE IsVolunteer = -1 ---not sure how to create if else statment into the sql queryUPDATE [dbo].[TEST2] SET event_role = 'Other' SELECT ID FROM dbo.TEST1WHERE ? = >2 ------try subquery but does not work because of multipe values errorsUPDATE [dbo].[TEST2]tSET event_role = 'Speaker'WHERE t.id IN (SELECT ID FROM dbo.TEST1 WHERE IsVolunteer = -1) AND event_role IS NULL[/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog