Creating A Table Inside A Stored Procedure

Feb 26, 2004

I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.

View 3 Replies


ADVERTISEMENT

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Transact SQL :: Getting Data From External Assembly Into Temp Table Inside Stored Procedure

Oct 8, 2015

I have a Custom .net assembly which retrieves some data, basically just a set of data containing, ID, value,

I need my stored procedure to return this data as a table.

My question what/how is fastest way to do this, my assembly can return anything you want, a datatable, a hashtable, list of(class) etc..

View 3 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Creating Stored Procedure That Inserts Value In Table

Feb 13, 2008

I am using vwde2008 and created db and table. i want to create a stored procedure that will insert values. I am bit lost on how write this.
this is my table info
table = BusinessInfo
Columns = BusinessID, BusinessName, BusinessAddress
how can i create a stored procedure ?

View 1 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

Creating New Table From Passed Variabe In MSSQL Stored Procedure

Mar 2, 2004

Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...

CREATE PROCEDURE dbo.CreateTable
@TableName NVARCHAR(50)
AS
BEGIN

create table @TableName ([id] int identity(1,1),[diller] int)

end
GO

THIS CANT BE TRUE !!!!!!!!

View 4 Replies View Related

Creating A Table Through Stored Procedure Shows An Syntax Error

May 26, 2000

hai guys,
i have written a stored procedure which creates a table ex:
USE PUBS
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC')
DROP PROCEDURE RC_STRPROC
GO
USE PUBS
GO
CREATE PROCEDURE RC_STRPROC
(@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35))
AS
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME')
DROP TABLE @TBLNAME
CREATE TABLE @TBLNAME
(@COLVAL1, @COLVAL2)
GO
it gives an syntax error at '@tblname'
can u guys tell me the problem

thanks
hiss

View 2 Replies View Related

Creating Stored Procedure With Temp Table - Pass 6 Parameters

Sep 9, 2014

I need to create a Stored Procedure in SQL server which passes 6 input parameters Eg:

ALTER procedure [dbo].[sp_extract_Missing_Price]
@DisplayStart datetime,
@yearStart datetime,
@quarterStart datetime,
@monthStart datetime,
@index int
as

Once I declare the attributes I need to create a Temp table and update the data in it. Creating temp table

Once I have created the Temp table following query I need to run

SELECT date FROM #tempTable
WHERE #temp.date NOT IN (SELECT date FROM mytable WHERE mytable.date IN (list-of-input-attributes) and index = @index)

The above query might return null result or a date .

In case null return output as "DataNotMissing"
In case not null return date and string as "Datamissing"

View 3 Replies View Related

Creating Stored Procedure To Use With Crystal Reports - Table Join

Dec 30, 2014

I am a newbie to SQL and I am trying to create a stored procedure to use with Crystal Reports.

I don't understand why I am having trouble joining tables to the Member table. These table joins have worked in the past.

Create Proc rpmb_FamilyPortraitDirectoryP1Test
(
@ChurchId int,
@StartFamilyName varchar(50),
@EndFamilyName varchar(50),
@MemberTypeId varchar(max) = Null,

[Code] ....

View 4 Replies View Related

Creating A Dynamic Global Temp Table Within A Stored Procedure

Sep 7, 2006

hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?

View 16 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Creating Nested Tables Inside Of A Existing SQL Server Express Table

May 26, 2007

Hello,

Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?

Sincerely,



James Simpson

Straightway Technologies Inc.

View 4 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

If Statement Inside A Stored Procedure!!!!

Mar 20, 2007

hi all,
i'm wondering if i can use one stored procedure in too cases, this is the senario:
i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user
if there a way to use if and else to make this happen
this is what i did so far:

CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as
if @indexType = 1

begin

SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin)
end
else
begin
SELECT * FROM table WHERE user = @user
end
GO

any suggestion will be very helpful
thanks

View 2 Replies View Related

RaiseError Inside Stored Procedure

Aug 5, 2006

I want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later.



here is an example

BEGIN procedure

Insert something and save it even if error is raised below

RaiseError('you made a mistake and need to do this.',16,-1)

END procedure



View 5 Replies View Related

How Can I Use A Stored Procedure Call Inside The WHERE?

Mar 31, 2008



Hi folks
I nead to call a stored procedure in a where statemene, but MSSQL dont like that.

My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code



ALTER PROCEDURE dbo.advsp_FilterRecordRights
@RequesterGUID Char(20),
@EntityGUID Char(20)
AS
BEGIN
if not Exists(Select *


from GUIDRightsH
where GUIDRightsH.EntityGUID = @EntityGUID and

GUIDRightsH.RequesterGUID = @RequesterGUID and
GUIDRightsH.RecProp <> 0)
Begin

Return 1
end


if not Exists(Select *


from UsergroupMembers
where UserGroupMembers.UsergroupGUID = @RequesterGUID and
dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1)
Begin

Return 1
end


Return 0

END

View 3 Replies View Related

Comparing Two Strings Inside A Stored Procedure

Aug 14, 2006

Basically I have two strings.  Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place.  Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data.  lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>"
Is there an way to accomplish this inside a stored procedure?

View 2 Replies View Related

Using Joins Inside Stored Procedure With Row_Number

May 18, 2007

This example is working: 
Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
 and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'.
I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.

View 2 Replies View Related

Iterate Result Set Inside Stored Procedure

Oct 26, 2007

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  

View 7 Replies View Related

How To Dynamically Create SQL Inside A Stored Procedure?

Feb 28, 2005

I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?



ALTER PROCEDURE dbo.spGetNextPageRecords

(
@pageSize int,
@previousMaxId int

)

AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN

View 4 Replies View Related

Executing A Variable Inside A Stored Procedure

May 7, 2005

Hello :) I need to do something like this:
 
CREATE PROCEDURE SelectCostumers @name varchar(100)
Declare @SQL = "SELECT Id, Name FROM Costumers"
AS
IF (@name IS NULL)
@SQL
ELSE
@SQL += "WHERE Name LIKE @name"
 
See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.
 
Thank you

View 2 Replies View Related

Commit Not Commiting Inside Stored Procedure

Jun 23, 2006

Hi Folks!

I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.

While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.

Here is my sample record in table :tblMsg
SenderRef <space> MsgStatusId <space> MsgType <space>Groupid
1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902

1281341 <space>18 <space>KBC-RCVD-ADM <space>191902<space>

and here is my code.

CREATE PROCEDURE msg_ResolveADMBeforeBlim
AS

DECLARE @blimGroupId AS INT
DECLARE @admSenderRef AS VARCHAR(50)

DECLARE admCursor CURSOR FOR
SELECT senderref
FROMtblMsg
WHERE msgstatusid = 18
AND msgtype = 'KBC-RCVD-ADM'
FOR UPDATE

OPEN admCursor

FETCH NEXT FROM admCursor INTO @admSenderRef

WHILE @@FETCH_STATUS = 0
BEGIN
--FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM
SELECT @blimGroupId = GroupID
FROM tblMsg
WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM'
AND SenderRef = @admSenderRef
PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef;
--UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3
BEGIN
UPDATE tblMsg
SET MsgStatusId = 3, GroupId = @blimGroupId
WHERE CURRENT OF admCursor
END
PRINT 'AFTER UPDATE INSIDE WHILE';
END

CLOSE admCursor
DEALLOCATEadmCursor

The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.

Any ideas why this would happen?

Thanks for your help.
Arun

View 3 Replies View Related

How To Query Two MS SQL DB's On The Same Server Inside A Stored Procedure

Jul 20, 2005

Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!

View 5 Replies View Related

Debugging Stored Procedure Inside SSMSE !!!

Dec 21, 2006

Hi for all

i just get the news that the Dec. CTP of SQL Server 2005 & tools is released !

but my dream is to see debugging stored procedure support inside the SSMSE as debugging applications inside Visual Studio 2005

Many many many thanks to Microsoft  and for the great efforts on the cool products

Mohamed 

View 3 Replies View Related

Query From Parameters Inside A Stored Procedure

Apr 30, 2008

Hi,

I have to write a stored procedure what repair a table.
It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID').
I do not found the way to write this procedure to can create statement from parameter of the procedure.
I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'.
If the parameter is in the 'WHERE', nothing problem.

My procedure is:
CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @OrphanRowCount int
select @OrphanRowCount = 1

while(@OrphanRowCount > 0)
begin
DECLARE KWCursor CURSOR FOR
SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);

OPEN KWCursor;
FETCH NEXT FROM KWCursor INTO @OrphanRowCount
CLOSE KWCursor
DEALLOCATE KWCursor

IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0)
BEGIN
exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)')
END
end
END
GO

How can I run fully parameterized queries from an SP.
I can make it only like exec('DELETE FROM' + @TableName + ...)


Thank you for any idea,
Imre

View 2 Replies View Related

Problem When Invoking Stored Procedure With Cursor From Inside .net

Nov 7, 2007

Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,

View 1 Replies View Related

To Pass Data To An Stored Procedure Inside Sql Server

Feb 2, 2004

I want to use a stored procedure inside Sql Server from my aspx page so that the data entered in the form goes to the database after submit.

My stored procedure (inside the SQL Server) inserts several fields in a table and it returns two variables.
What code I need to write in order to pass the data from the form to the stored procedure inside the Sql Server? And to store the two returned values?


Thanks

View 7 Replies View Related

How To Pass The Value Of A Text Box Inside A FORMVIEW To A SQL Stored Procedure

May 28, 2006

Hi,
I'm new to ASP.NET 2.0. I have a sqldatasource and a formview controls on a web page and inside the formview control I have  two textboxes. When I click the UPDATE button in the formview, I'd like to be able to retrieve the values of the textboxes inside the formview control and pass these values to a 'Update' SQL stored procedure defined in the Sqldatasource.
Does anyone know how I can do this? Hope my question is clear.
Thanks in advance.
hakl

View 1 Replies View Related

SELECT Query Stmt Inside Stored Procedure

Nov 21, 2005

Friends,

What are the possible usuages of a SELECT query stmt inside a stored procedure ??

How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??

can any one throw some lite on this ..

Thanks,
SqlPgmr

View 1 Replies View Related

How To Create A Second Independent Transaction Inside A CLR Stored Procedure?

Nov 29, 2005

I use the context connection for the "normal" work in the CLR procedure.

View 7 Replies View Related

Help-convert Date Inside Stored Procedure Problem

May 7, 2008

problem convet date



Code Snippet
DECLARE
@y_Date [varchar](4),@M_Date [varchar](2),@d_Date [varchar](2)
DECLARE @mydate [datetime]
set @y_Date='2008'
set @M_Date ='8'
SET @d_Date='05'
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS DATETIME)
select @mydate

2008-05-08 00:00:00.000
and it look ok


but whan i do this



Code SnippetEXECUTE [dbo].[testpro] @mydate

the date not pass properly !!



Code Snippet
DECLARE
DECLARE @mydate [datetime]
set @mydate = getdate()

2008-05-08 01:20:35.870
like this it work ok






what is the problem with tis line ?
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS

tnx

View 7 Replies View Related

Insertion And Deletion Of Records Inside A Single Stored Procedure

Mar 25, 2008

Hi,
I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days.
 Thanx in Advance

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved