Hi All,
I have to execute one of the stored procedure within another stored procedure.
And have to store it into some table variable...
How to do that.pls give me the syntax...
Thanks and reagards
A
I'm trying to write a SQL that stores a result from a stored-procedureinto a temporary table.Is there any way of doing this?
View 3 Replies View RelatedHi
i m new in sqlserver databases
i need to know how to "store output of data from stored procedure in a text file "
suppose i have a stored procedure which has to cuculate some out put from some tables and in the end i want that all out put in comma delimited text file.
my databse name is check1
i need help please
thanks in advance
take care
bye
hi
in my front end i have one text area where user can input his sql statements. for samller data its working. the data type of coulmn is Text in sql server 2005. but problem is when i try to store this data its showing error.i meant not storing data at all. if i cut some of line from this then in this case it stores the data .what should i do to overcome this problem
thanx a lot.
DECLARE @sTemp VARCHAR(10)
DECLARE @nMemberID INT
DECLARE @nPartnerID INT
DECLARE @nDocumentTypeNo INT
DECLARE @nDocumentFormat INT
DECLARE @sEmailAddress VARCHAR(200)
SET @sTemp = '{*MEMBERID*}'
IF UPPER(@sTemp) = 'NULL'
SET @nMemberID = NULL
ELSE
SET @nMemberID = CAST(@sTemp AS INT)
SET @sTemp = '{*PARTNERID*}'
IF UPPER(@sTemp) = 'NULL'
SET @nPartnerID = NULL
ELSE
SET @nPartnerID = CAST(@sTemp AS INT)
SET @sTemp = '{*DOCUMENTTYPENO*}'
IF UPPER(@sTemp) = 'NULL'
SET @nDocumentTypeNo = NULL
ELSE
SET @nDocumentTypeNo = CAST(@sTemp AS INT)
SET @sTemp = '{*DOCUMENTFORMAT*}'
IF UPPER(@sTemp) = 'NULL'
SET @nDocumentFormat = NULL
ELSE
SET @nDocumentFormat = CAST(@sTemp AS INT)
SET @sEmailAddress = '{*EMAILADDRESS*}'
IF EXISTS(SELECT *
FROM ProtocolSettings
WHERE ((@nMemberID IS NULL AND MemberID IS NULL) OR MemberID = @nMemberID)
AND ((@nPartnerID IS NULL AND PartnerID IS NULL) OR PartnerID = @nPartnerID)
AND ((@nDocumentTypeNo IS NULL AND DocumentTypeNo IS NULL) OR DocumentTypeNo = @nDocumentTypeNo)
AND ((@nDocumentFormat IS NULL AND DocumentFormatNo IS NULL) OR DocumentFormatNo = @nDocumentFormat)
AND ProtocolSettingNo = 307)
BEGIN
UPDATE ProtocolSettings
SET SettingValue = @sEmailAddress
WHERE ((@nMemberID IS NULL AND MemberID IS NULL) OR MemberID = @nMemberID)
AND ((@nPartnerID IS NULL AND PartnerID IS NULL) OR PartnerID = @nPartnerID)
AND ((@nDocumentTypeNo IS NULL AND DocumentTypeNo IS NULL) OR DocumentTypeNo = @nDocumentTypeNo)
AND ((@nDocumentFormat IS NULL AND DocumentFormatNo IS NULL) OR DocumentFormatNo = @nDocumentFormat)
AND ProtocolSettingNo = 307
END
ELSE
BEGIN
INSERT INTO ProtocolSettings (ProtocolSettingNo, MemberID, PartnerID, DocumentTypeNo, DocumentFormatNo, SettingValue)
VALUES(307, @nMemberID, @nPartnerID, @nDocumentTypeNo, @nDocumentFormat, @sEmailAddress)
END
All,
I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...
"Insert exec cannot be nested"
I suspsect this is because I have a Insert Exec statement inside my stored procedure...
Is there any way to come over this issue ?
syntax of my code...
create table #Temp1 (ID int)
insert into #Temp1
EXEC SP1
when I try to run the above code I get the error "Insert exec cannot be nested"
SP syntax :
Create Procedure SP1
as
Begin
create table #Temp2
statements.....
Insert into #temp2
exec SP<Name>
staments.. cont...
END
Hi All
I have a stored procedure, sp_GetNameDetail, which return a one row, multiple columns result set.
Yet I have another storede procedure which would call sp_GetNameDetail, and would like to access this result set. Is there a way I can do this?
Thanks,
Hello evry1.
i m new to SQL Server2000. plzz tell me where i m
wrong in this SP . this procedure is not giving any
error but it is not showing any result. plzz help me n
give me any idea if u know how i can solve my problem
best regards
sadaf
n here is the procedure
CREATE PROCEDURE Search12
( @signup char(50),
@user_name1 [char](50),
@gender1 [char](6),
@place1 [char](100),
@email_address1 [char](50),
@relegion1 [char](50),
@political_view1 [char](50),
@passion1 [varchar](150),
@sports1 [varchar](150),
@activities1 [varchar](150),
@books1 [varchar](150),
@music1 [varchar](150),
@tv_shows1 [varchar](150),
@movies1 [varchar](150),
@cuisines1 [varchar](150),
@intrested_in1 [varchar](150),
@education1 [char](100),
@college_university1 [char](50),
@occupation1 [char](50),
@industry1 [char](50),
@job_desc1 [char](50),
@career_intrest1 [char](100),
@latitude1 [decimal],
@longitude1 [decimal])
AS
if (@user_name1 is not null and len(@user_name1) > 0)
begin
select * from [profile] where [user_name] like
'%@user_name1%' and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@gender1 is not null and len(@gender1) > 0)
begin
select * from [profile] where gender = @gender1 and
place like '%@place%' and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if(@email_address1 is not null and
len(@email_address1) > 0)
begin
select * from [profile] where [email-address] like
'%@email_address1%' and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if(@relegion1 is not null and len(@relegion1) > 0)
begin
select * from [profile] where relegion = @relegion1
and place like '%@place%' and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@political_view1 is not null and
len(@political_view1) > 0)
begin
select * from [profile] where political_view =
@political_view1 and place like '%@place%' and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@passion1 is not null and len(@passion1) > 0)
begin
select * from [profile] where passion like
'%@passion1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if(@sports1 is not null and len(@sports1) > 0)
begin
select * from [profile] where sports like '%@sports1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@activities1 is not null and len(@activities1) >
0)
begin
select * from [profile] where activities like
'%@activities%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@books1 is not null and len(@books1) > 0)
begin
select * from [profile] where books like '%books1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@music1 is not null and len(@music1) > 0)
begin
select * from [profile] where music like '%@music%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if(@tv_shows1 is not null and len(@tv_shows1) > 0)
begin
select * from [profile] where tv_shows like
'%@tv_shows1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@movies1 is not null and len(@movies1) > 0)
begin
select * from [profile] where movies like '%@movies1%'
and place = @place1 and latitude=@latitude1 and
longitude=@longitude1 and signup_name != @signup;
end
if (@cuisines1 is not null and len(@cuisines1) > 0)
begin
select * from [profile] where cuisines like
'%@cuisines1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@intrested_in1 is not null and len(@intrested_in1)
> 0)
begin
select * from [profile] where intrested_in =
@intrested_in1 and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@education1 is not null and len(@education1) > 0)
begin
select * from [profile] where education
like'%@education1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@college_university1 is not null and
len(@college_university1) > 0)
begin
select * from [profile] where institution like
'%@college_university1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@occupation1 is not null and len(@occupation1) >
0)
begin
select * from [profile] where occupation like
'%@occupation1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@industry1 is not null and len(@industry1) > 0)
begin
select * from [profile] where industry like
'%@industry1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@job_desc1 is not null and len(@job_desc1) > 0)
begin
select * from [profile] where job_desc like
'%@job_desc1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
if (@career_intrest1 is not null and
len(@career_intrest1) > 0)
begin
select * from [profile] where career_intrest like
'%@career_intrest1%' and place = @place1 and
latitude=@latitude1 and longitude=@longitude1 and
signup_name != @signup;
end
GO
Hi!How can I save a result set from a stored procedure into a table?Can you show me some examples?Thank you!Darko
View 2 Replies View Related
i made a new stored procedure and execute it. it works great but where can i see the result ?
i want to see the number of Count(*) in a window inside the SQL Server Managment studio, can i ?
my stored procedure is as follows :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetTotalMovieCreations]
AS
select count(*)
from tbl_Animations
where statusID = 1
and ToolID = 1
and ToolID = -1
and isDeleted = 0
thanks...
My stored procedure displays two result sets. How can i use that result sets in my 3-tier application. I want to bind first resultset to repeater control and second to label control. I am using SqlDataReader...
View 6 Replies View RelatedHi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View 11 Replies View RelatedHello all, I have the following Stored Procedure that has been working perfectly for the last year:
CODE
====================================================
DELETE FROM tblReportMainMembers
INSERT INTO tblReportMainMembers (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM tblCart INNER JOIN tblMemberships ON tblCart.membership = tblMemberships.idWHERE (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444'
UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname, --tblReportMainMembers.userid = tblRecords.id, tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city, tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip, tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone, tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid
UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id
UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account
SELECT RTRIM(emplid) AS EMPLID, RTRIM(userid) AS USERID, RTRIM(fname) AS FNAME, RTRIM(lname) AS LNAME, RTRIM(membership) AS MEMBERSHIP, CAST(price AS varchar(12)) AS PRICE, RTRIM(approvecode) AS APPROVECODE, CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE, CONVERT(varchar(20), wpecend, 101) AS WPECEND, RTRIM(address) AS ADDRESS, RTRIM(city) AS CITY, RTRIM(state) AS STATE, RTRIM(zip) AS ZIP, RTRIM(homephone) AS HOMEPHONE, RTRIM(officephone) AS OFFICEPHONE, RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname
As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users.
But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again.
Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it.
Any ideas? Thanks in advance.Richard M.
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code...
CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12)
AS
BEGIN
declare @str nvarchar(2000)
declare @a varchar(10)
select @str = 'select * from openquery(kronos," SELECT decode(a.PAYCATID,1,'+ "'ST'"+",'OT'"+') paycode ,f_calc_hrs(sum(a.AMOUNT)) hrs '
select @str = @str + 'FROM TOTALEDPAYCATEDIT a ,LABORACCT b ,LABORLEVELENTRY c '
select @str = @str + 'where ( c.NAME =' + "'"+'' + @wono +"') "
select @str = @str + 'and (a.LABORACCTID = b.LABORACCTID) '
select @str = @str + 'and c.LABORLEVELENTRYID = b.LABORLEV3ID group by a.PAYCATID ' + '' + '")'
/*
exec sp_executesql @str
*/
create table #t (paycode varchar(7), hrs varchar(255))
insert into #t exec sp_executesql @str
select @a = hrs from #t where paycode='ST'
drop table #t
print @a
if i call exec sp_execute @str, it will output this
PAYCODE HRS
------- --------
ST 08: 30
OT 54: 00
(2 row(s) affected)
I want those #'s store into a temp variable OR be passed to another procedure.
HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse.
Thanks
I would like to make a selection of records returned by a stored procedure.
E.g.
SELECT Name FROM EXEC somestoredprocedure @age = 25
This is wrong, but is there a way to do this?
Maarten
Hi Guys..
How Can i Execute a result from a StoredProcedure... I got a sp that generates drop index and pk from all tables in the DB..
I got this results from running (sp_dropallindex) like this:
ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 GO
ALTER TABLE Table2 DROP CONSTRAINT PK_table2 GO
DROP INDEX table1.index1 GO
DROP INDEX table1.index2 GO
I need to execute that result.. I know that i can copy/paste into Query Analyzer and then run it but how can i handle that result and run all in shot ...
I tried something like this:
DECLARE @DROP AS VARCHAR(8000)
SET @DROP='exec sp_drop_allindex'
EXECUTE (@DROP)
and i see the same out , but my indexes and PK still there ... i'm confused about it ..
PLEASE HELP ME OUT :D
This is a strange issue I'm encountering. I have a websphere application that calls a basic Stored Procedure in SQL Server 2000. The SP works fine. Now, if I create a TEMP table in the SP, I no longer get a resultset. No results are returned by the websphere. Even if I do not use the temp table... that is, I just create the temp table like so:
SELECT region_code,
OperGroup_Code,
country_name,
MajorGroup_Name,
RptgEntity_Name,
shell_code,
CRC,
' ' as right_type
INTO #tmpTShell FROM TShell WHERE 1=0
But I then grab my records using the query that works (querying another table), I still get NULL for records returned. Only when I comment out the above TEMP table creation do I get the results back in websphere. Another strange thing is that this works fin in SQL analyzer. WRegardless of whether I have the temp table creation commented out or not, it always works in the Query Analyzer whe I call the SP. It just seems to effect WebSphere or my java code in that it returns null if I create the temp table.
Has anyone ever experienced anthying like this? Any help would be greatly appreciated!
Thanks.
I know how to write a stored procedure that does various things to a database. I know that a stored procedure returns a value of 0 by default if it executes successfully, and a non-zero value otherwise. I know you can use output variables to return other values from a stored procedure. I am moderately familiar with these things.
But... how do I fashion it if I'm calling the stored procedure from VB.NET in a web application, and I don't just want a couple of variable values, I want the whole result set?
I know there's #temporaryTables that ...exist within the scope of the stored procedures... ##Globaltemptables... regular_tables... and @tableVariables. I'm reading furiously to figure out what these things...all...do... and I'm leaning in the direction of ... a variable table as an output variable, but I just don't know... how and what I can stuff that into in the front end so I can shove it into the nice and neat grid view thing.
(fyi, I'm trying to return a consolidated table of available rooms fitting the user's specified reservation dates and amenity preferences - that part, I've gotten done like a boss. It's...getting it back to the front end I'm struggling with.)
Hi,
I have a problem with dealing with result sets returned from stored procedures.
I have a procedure like:
CREATE PROCEDURE SampleProcedure
AS
BEGIN
SELECT * FROM SampleTable
END
GO
By executing this stored porocedure is returned result set containing data from SampleTable table. (EXECUTE SampleProcedure)
The returned resultset can be seen in Query Analyzer and can be handled from ADO.NET without any hesitate.
But I can't use this result set from other stored procedure. I tried:
SELECT * FROM (EXEC SampleProcedure)
But there is sintax error in select statement.
Does anybody know, how to store the result set into a teporary table or select it by SELECT statement?
Thanks.
MarF.
Pls give some samples
View 3 Replies View RelatedHi,I have one stored procedure that calls another ( EXEC proc_abcd ). I wouldlike to return a result set (a temporary table I have created in theprocedure proc_abcd) to the calling procedure for further manipulation. Howcan I do this given that TABLE variables cannot be passed into, or returnedfrom, a stored procedure?Thanks,RobinExample: (if such a thing were possible):DECLARE @myTempTable1 TABLE ( ID INT NOT NULL )DECLARE @myTempTable2 TABLE ( ID INT NOT NULL )...../*Insert a test value into the first temporary table*/INSERT INTO @myTempTable1 VALUES ( 1234 )...../*Execute a stored procedure returning another temporary table ofvalues.*/EXEC proc_abcd @myTempTable2 OUTPUT......../*Insert the values from the second temporary table into the first.*/SELECT * INTO @myTempTable1 FROM @myTempTable2
View 1 Replies View RelatedI am trying to get data back from stored procedure output arguments. I am in a Visual C++ enviroment calling the stored procedure via the ODBC function SQLExecDirect. When the stored procedure has only a single output argument, everything works fine. When there are multiple output arguments, I get an error "Invalid Cursor State' from the SQLFetch. This is telling me there is no result set.
Below is a pseudo code fragment which shows what I am doing.
I want to use SQLGetData to convert the data into C variables. I do not want to use SQLBindParameter or Parameter Markers.
Sql = "DECLARE @outarg1 int
DECLARE @outarg2 datetime
{CALL ProcName('Inarg1','Inarg2',.......@outarg1 OUTPUT,@outarg2 OUTPUT ) }
SELECT @outarg1,@outarg2 " ;
rc = SQLExecDirect(hstmt,Sql,strlen(Sql) )
if( rc == SQL_SUCCESS)
{
rc == SQLFetch(hstmt);
if(rc == SQL_ERROR)
{
SqlGetDiagRec(.........);
}
}
.....
When there is one out parameter, everything works OK.
When there are multiple out parameters, I get a SQL error state 24000 which is "Invalid Cursor State". This tells me there is no result set for the Fetch to act on.
The stored procedure is doing a select for each out parameter.
Any Idea why I get an error when using more than one out parameter
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail). How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */
SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN
dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN
dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN
dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */
SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/
SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)SET @p_message = @p_message + "Comment: " + @comment + char(13)PRINT "subject: " + @p_subject + char(13)PRINT "recip: " + @p_recipients + char(13)PRINT "msg: " + @p_message + char(13)/*Send the email*/
Execute master..xp_sendmail @recipients = @p_recipients, @message = @p_message, @subject = @p_subject END
GO
I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.
View 2 Replies View RelatedHow can I find the result columns from code? Visual J++ Seems to be able to do that. ADO doesn't want to do it for me, neither does ODBC's SQLProcedureColumns().
Any solutions?
Hey guys!
I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck.
I've created a hierarchal listbox form that drills down From
Product - Colour - Year.
based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes.
So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER.
I want that product number to be used to populate the grid view. Is there away for me to do this?
Thanks in advanced everybody!
Hi,
I am new to stored procedure.
My stored procedure is returning me the list of tables names where the given tables PK is used as FK (can be null), based on the result of stored procedure I need to update the tables.
Following Stored procedure will return the list of tables where the usertable's PK is referred as a FK
I want to use the returned table name and update the set eh FK's value = null.
Some how it is not working , can some one point me out on correct solution.
CREATE PROCEDURE [dbo].[sp_delete_data]
AS
DECLARE @update_Var table (
REF_Table nvarchar(50),
FK_Column varchar(25),
PK_Table varchar(25),
PK_Column varchar(25),
Constraint_Name varchar(50));
insert into @update_Var
SELECT
FK.TABLE_NAME,
CU.COLUMN_NAME,
PK.TABLE_NAME,
PT.COLUMN_NAME,
C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN(
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME='usertable';
Select * from @update_Var
Thanks
Santosh Maskar
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.
SELECT Max(Number)
FROM Employee
Hi,
I have written a stored procedure to upload a file to a table.
I am quering this table and want to download the result to a file.Can someone please help me to do this ?
Thanks,
Naguveeru
I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeField FROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet '
CREATE PROCEDURE [dbo].[SomeSP]
@SomeParam int
AS
BEGIN
SET NOCOUNT ON;
declare @SomeScalar int
exec @SomeScalar = SomeSPThatReturnsAnIntAndAResultSet @SomeParam
if @SomeScalar = 0
BEGIN
SELECT SomeField FROM SomeTable
END
END
this is my problem in stored procedure
1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE
2) I need to see only the employee than in the table v_un
3) i see all the employee
4) problem in this line ( [new date] = @mydate2,[new_shift2] = )
i see 2 rows of result from fields [new date] + ,[new_shift]
how to see only 1 field for each field
new date new_shift new date empid name
---------------------------------------------------------------
2007-12-01 99 2007-12-02 99 2568947 aaa
2007-12-01 99 2007-12-02 99 2845209 bbbb
2007-12-01 99 2007-12-02 99 4807756 ccc
2007-12-01 99 2007-12-02 99 9819590 ddd
2007-12-01 99 2007-12-02 99 10055648 eee
2007-12-01 99 2007-12-02 99 10815413 ffff
2007-12-01 99 2007-12-02 99 11070042 gggg
2007-12-01 99 2007-12-02 99 11162047 hhh
-------------------------------------------------------------------------------
i need to see only one for each field
new date new_shift id name
---------------------------------------------------------------
2007-12-01 99 2568947 aaa
2007-12-01 99 2845209 bbbb
2007-12-01 99 4807756 ccc
2007-12-01 99 9819590 ddd
2007-12-02 99 10055648 eee
2007-12-02 99 10815413 ffff
2007-12-02 99 11070042 gggg
2007-12-02 99 11162047 hhh
Code Block
DECLARE @yeara [varchar](4)
DECLARE @month1 [varchar](2)
DECLARE @day1 [varchar](2)
DECLARE @day2 [varchar](2)
DECLARE @day3 [varchar](2)
DECLARE @mydate1 [datetime]
DECLARE @mydate2 [datetime]
set @yeara ='2007'
SET @month1 ='12'
Set @day1 ='1'
Set @day2 ='2'
set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103)
set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103)
SELECT
SilokE
CASE
WHEN (empid IN (SELECT empid FROM SilokEWHERE (shift = 51 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))
THEN 2
else 99
END,
[new date] = @mydate2,[new_shift] =
CASE
WHEN (empid IN (SELECT empid FROM v_un
WHERE (shift = 11 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 12 )))
THEN 2
else 99
END,
SilokE.empid, SilokE.Fname
FROM SilokE
Created a stored procedure which returns Selected table from database.
I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure
(
@condition varchar(20),
@ID bigint,
@date1 as datetime,
@date2 as datetime
)
AS
/* SET NOCOUNT ON */
IF @condition LIKE 'all'
SELECT CllientEventDetails.*
FROM CllientEventDetails
WHERE (ClientID = @ID)
IF @condition LIKE 'current_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom <= ISNULL(@date1, EventFrom)) AND
(EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE ''
SELECT CllientEventDetails.*
FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
Greetings
I have a SQL Server stored procedure that performs several queries and therefore returns several "result sets" at any one time.
Wiring it up via ADO.NET I populate a DataSet with a number of items in the Tables collection - which is great - and I can give each item a name for identification purposes once the DataSet is populated.
But I'd like to know if there is some way I can set the names of each result set *within the text of the stored procedure*, i.e. before the DataSet gets populated.
Any help greatly appreciated.
Stuart
I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.
Here is an example:
-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END
-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc
--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))
-- this call will succeed
INSERT INTO testTable EXEC testProc