Exec Stored Precedures On MSSQL2000 From Oracle
Jul 14, 2003Hello,
Does anybody know, how can I execute stored procedures that are on MSSQL Server 2000 from Oracle9i SP ? Can you show me an examle ?
Thanks for any help.
ADVERTISEMENT
Don't Know How To Get It Work With Stored Precedures
Sep 14, 2006Wael writes "Hi all.
i am using SQL server 2000 database and crystal reports XI.
The purpose of this report is to measure the loyalty of the clients to buy and use a prepaid cards from a telephone company, the company concerned to know how it perform on a various range measures.
in technical saying. we have 2 tables (CLL, CRD) the first one save a record about each call made by a certain card, including card no, source no(very important), destination no , date performed, duration and so on ...
the second table contains information about card itself like card no, card price, type , pricing category, time open, date closed (very important), brand, and so on....
as i mentioned there is only 2 fields in concern in our report.
*the first is the source no as an indication of a client (assume that the client will always call from his home to outer line or whatever .... )
*the second is the date closed as it says that a certain card finishes its value at this date, it will be the base date to build your periods on.
the report should do the following:-
1- prompt the user for a period range to analyze.
2- prompt the user to specify how this range will be analyzed (yearly, quarterly, monthly, weekly, ....).
3- cut the range into slices as determined in the second period.
4- for each period, compare it with the previous periods (as whole from the start of the range to just before the current period) to get the following 3 counts:
- new clients : didn't call at the previous period but called in the current period.
- existing clients : called in previous period and still calling in the current period.
- left clients : called in previous period and didn't in the current period .
5- and so on till the last period in the range.
6- this data preferred to be displayed graphically, if not as a crosstab.
7- note that : i said certain client called in a certain period , if he used a card and call at least one time in a that period.
please know that i am a new bie in stored procedures, so if there is a possibility for that report to run with stored procedure, i will be grateful if it is in some details.
at the end , i am so sorry for that detailed explanation, but i want to clarify my poit. please any help, i will appreciate it.
thanks for your time."
Transaction On Stored Precedures
Jul 20, 2005Hi allquestion regarding how transactions work in SQL Server...stored procedure below that generates unique IDs for messages wesend to the backend It looks like sometimes, the number generated isidentical for a messages that were sent simultaneously. Since thisseems like a concurrency issue, am I correct in thinking that usingSQLtransactions within the stored procedure code will solve this problem?The stored procedure is outlined below, the code in red is what Ithink should go in to solve the concurrency problem.CREATE PROCEDURE [dbo].[praCCGetNewCDMIndex]@newdate intASSET NOCOUNT ONDECLARE @lastdate intDECLARE @lastindex intDECLARE @newindex intBEGIN TRAN CDMIndexSELECT @lastdate = N_JULIANDATE FROM tbl_CC_CDMIndexesSELECT @lastindex = N_INDEX FROM tbl_CC_CDMIndexesIF @lastdate = @newdateSET @newindex = @lastindex + 1ELSESET @newindex = 1UPDATE tbl_CC_CDMIndexesSET N_JULIANDATE = @newdate,N_INDEX = @newindexSELECT N_INDEX FROM tbl_CC_CDMIndexesCOMMIT TRAN CDMIndexSET NOCOUNT OFFGO
View 2 Replies View RelatedMssql2000 And Oracle 10g On Same Server
Aug 10, 2006I have mssql2000 running on a Windows 2003 server and now have a requirement to run an Oracle 10g database as well. Is it possible to run both mssql2000 and oracle 10g on the same server without running into any conflicts or will the two programs cause errors with each other?
Anyone have any experience with this? Oracle says it's technically possible but the tech had never seen it done.
Scott
Difference Between MSSQL2000, MSSQL2005 Developer Edition (was MSSQL2000 Question)
Jul 6, 2006Okay heres the problem I'm facing. I have GoDaddy as my hosting company. They only support MSSQL2000 but I just recently bought MSSQL2005 Developer Edition. And I was wondering what is the main difference between them two. I didn't find anything on Google. And the only thing is I can't find a better host. Cause I have 50GB of space and 500GB of bandwidth. And I don't want to lose that for just MSSQL2005 support cause my host doesn't/ Have that. So im stuck like chuck. Is there really any difference?
View 1 Replies View RelatedEXEC AT For Calling Oracle SP's
Nov 12, 2007
I have seen a couple of references to "EXEC AT" in relation to linked servers, that can be used to call Oracle Stored Procedures, in the TechNet postings. Based on the postings, this is new functionality in SQL Server 2005, but I am unable to find any reference to this in the Online Documentation for SQL Server 2005.
Could someone point me in the right direction for documentation of the feature?
TIA
Paul Watje
Stored Procedures Using EXEC
Aug 8, 2007Hi: I would like to know if I invoke a stored procedure using "EXEC [database].[user].[StoredProcedure] param1, param2..." is just like to use the ADO.NET objects: SqlCommand myCommand = new SqlCommand("StoredProcedure", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add("@param1", param1);myCommand.Parameters.Add("@param2", param2); ..... Thanks
View 3 Replies View RelatedExec Stored Procedure
Nov 5, 2007Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
if @id=1 insert into ....else if @id=2 update...-----------------------orif @id=1 exec InsertProcedureelse if @id=2 exec UpdateProcedurePlease help
Exec Stored Proc And Getting The Value
Mar 21, 2008I've been bangging my head against the wall on this one and its probably something really easy but for the love of god, I can't figure it out.
I have the following stored procedure
ALTER PROCEDURE [dbo].[InsertUserProjectPhase]
(
@ProjectPhaseID int,
@ProductionEngineerUserID int,
@QuantityCompleted decimal(18, 2),
@BillableHours decimal(18, 2),
@TotalWorkingHours decimal(18, 2),
@DateUpdated datetime,
@UpdatedByUserID int,
@IsDeleted bit
)
AS
SET NOCOUNT OFF;
INSERT INTO User_Project_Phase
(ProjectPhaseID, ProductionEngineerUserID, QuantityCompleted, BillableHours, TotalWorkingHours, DateUpdated, UpdatedByUserID, IsDeleted)
VALUES (@ProjectPhaseID,@ProductionEngineerUserID,@QuantityCompleted,@BillableHours,@TotalWorkingHours,@DateUpdated,@UpdatedByUserID,@IsDeleted);
SELECT ID FROM User_Project_Phase WHERE (ID = SCOPE_IDENTITY())
How can I call the above stored procedure (from within another stored procedure) AND save get the return value (the ID). I tried the following:
exec @newProjectPhaseID = dbo.InsertProjectPhase @ProjectID=3796,@PhaseID=@PhaseID,@EstimatedQuantity=@EstimatedQuantity,
@EstimatedDuration=@EstimatedDuration,@TotalQuantityCompleted=@TotalQuantityCompleted,
@TotalBilledQuantity=@TotalBilledQuantity,@QuantityType=@QuantityTypeID,@StatusID=@StatusID,
@CreatedByUserID=18569
Where @newProjectPhaseID was declared at the top as int. The numbers you see in there will be replaced with variables. They are there now because I'm executing the sp directly in management studio.
Of course, the above doesn't work. I was hoping that the return value form InsertProjectPhase would be saved in @newProjectPhaseID but I get an error.
Any help will be appreciated. THANKS A MILLION!
-Psion
Exec Stored Proc
Sep 30, 2004I need some help with the following store proc, something is wrong but I just dont see it.
Thanks
btw I am no expert at sp so something might be complety wrong.
ALTER PROCEDURE dbo.GetSearchByDateRange
(
@strColumnNamenvarchar (50),
@dtDate1 Date,
@dtDate2 Date
)
as
EXEC ('SELECT * FROM Customers WHERE ' + @strColumnName + ' BETWEEN ' + ''' + @dtDate1 + ''' + ' AND ' + ''' + @dtDate2 + '''')
Exec Stored Procedure
Jan 10, 2006Hi,
Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.
Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.
Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).
Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?
Thanks
Rich
Exec Stored Proc
Aug 7, 2007Hi Guys, I created the following stored proc which calls another stored procs as shown below.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Create Procedure [dbo].[rpt_CompareDB](@tbl_name varchar(400),@DB_name1 varchar(400),@DB_name2 varchar(400))
AS
declare @result1 varchar(8000),
@result2 varchar(8000),
@table_name varchar (400),
@DB1 varchar(400),
@DB2 varchar(400),
@table_final1 varchar(400),
@table_final2 varchar(400),
@DB_detail1 varchar(400),
@DB_detail2 varchar(400)
set @table_name = @tbl_name
set @DB1 = @DB_name1
set @DB2 = @DB_name2
set @DB_detail1 = @DB1 + '.mfgq_live'
set @DB_detail2 = @DB2 + '.mfgq_live'
set @table_final1 = @DB_detail1 + '.dbo.' +@table_name
set @table_final2 = @DB_detail2 + '.dbo.' +@table_name
exec GetColumnNamesString @DB_detail1,@table_name,@result1 output
exec GetColumnNamesString @DB_detail2,@table_name,@result2 output
exec CompareTables @table_final1,@table_final2,@result1,@result2
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
but, when I tried to excute it, I am getting an error.
Here is how I'm trying to exec the stored proc.
execute dbo.rpt_CompareDB
@tbl_name = 'Attribute'
,@DB_name1 = 'Green'
,@DB_name2 = 'devlue02'
FYI, It works fine if you run the query itself instead of executing the stored proc.
Any idea is appreciated
Exec Stored Procedure
Aug 20, 2007Hi,
I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!
I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!
The ole db command calls a stored procedure with two input variables:
exec stp_updedgrsholds status, temp_cr_num
from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.
but nothing gets updated in the database.
If I call the stored procedure the following way
exec stp_updedgrsholds 'C', 87
It updates fine! I have tried a number of different way with @ symbols and assignment p_status = @status
but nothing seems to work.
Any ideas are much appreciated.
Ninder Bassi
How To Exec A Stored Procedure
May 23, 2007hi,
how do I exec stored procedure that accept parameter and return a single value?
here is example of report
stu_id = ******
stu_name = ****
subject | marks
aa****** | call sp_mark and return student mark for that particular student id and subject
bb****** | call sp_mark and return student mark for that particular student id and subject
cc****** | call sp_mark and return student mark for that particular student id and subject
thks,
SQL Error When Using EXEC In Stored Proc
Jul 10, 2006I have the following stored proc that is providing the following error - Server: Msg 156, Level 15, State 1, Line 79[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'.
The sp works fine if the DateOfBirth elements are removed. I think it is something to do with the way in which the date variable is incorporated into the string that will be executed by the EXEC command but I'm unsure as to what is wrong.
CREATE PROCEDURE dbo.GetPersonsByName (@FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @DateOfBirth datetime=NULL)
AS
EXEC ('SELECT PersonId, Title, FirstName, FamilyName , AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeath, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, Hospital, HospitalNo, AltHospital, AltHospitalNo, EthnicGroup, Citizenship, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated
FROM vw_GetPersons WHERE FirstName LIKE ''%' + @FirstName + '%'' AND FamilyName LIKE ''%' + @FamilyName + '%'' AND DateOfBirth = '+ @DateOfBirth +' ORDER BY FamilyName, FirstName')
GO
Stored Procedure Exec An SQL Statement
Oct 2, 2007I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?
Exec Stored Proc In Different Database
Apr 17, 2001I have a set of stored procedures copied into several databases (similar table structures but different data). I would like to avoid maintaining so many versions of the same procedures, so want to put the stored procs in a common database and execute it from there, e.g.
exec common_db..the_procedure
However, when you do that, the procedure executes in the context of common_db, not in the context of the calling proc's database. Is there a way of calling a procedure so that the calling proc's context is used?
Exec Stored Proc Question
Dec 4, 2000I am working on a project to re-code SQL Anywhere to T-SQL (SQL Server 2000) and am encountering many instances where the original code contained a SELECT statement that calls a stored procedure. T-SQL does not allow calling a procedure from a SELECT statement as far as I can see. Does anyone have any good ideas on working around this??
Thanks
Timeout While Exec Stored Procedure
Mar 12, 2008I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.
How To Exec Stored Proc Dynamically
Jul 23, 2005HelloI have 2 procedures setup in master database, sp_RebuildIndexesMain andsp_RebuildIndexesSubThe Sub just shows and execute DBCC commands for passed databasecontextsp_RebuildIndexesSub(@listOnly bit=0, @maxfrag Decimal=30.0)This runs fine if I do pubs..sp_RebuildIndexesSubHowever when run thru. the Main proc, I get Incorrect syntax near'pubs'.The main proc isCreate Proc sp_RebuildIndexesMain(@dbName sysname, @listOnly bit=0,@maxFrag Decimal=30.0)AsBeginSet NOCOUNT ONDeclare crDbs CURSOR ForSelect CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATAWhere CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model','distribution', 'Northwind', 'pubs')And CATALOG_NAME Like @dbNameDeclare @execstr nvarchar(2000)Open crDbsFetch crDbs INTO @dbNameIf (@@FETCH_STATUS<>0) --Then no matching databasesBeginClose crDbsDeallocate CrDbsPrint 'No databases were found that match ''' + @dbName + ''''Return -1EndWhile(@@FETCH_STATUS=0)BeginPrint Char(13) + 'Rebuilding indexes on ' + @dbNamePrint Char(13)Set @execstr = @dbName + '..sp_RebuildIndexesSub 'EXEC sp_executesql @execstr, N'@listOnly bit, @maxFrag Decimal',@listOnly, @maxFragFetch crDbs INTO @dbNameEndClose crDbsDeallocate CrDbsReturn 0EndthanksSunitJoin Bytes!
View 5 Replies View RelatedExec SQL Task (stored Procedures)
Jan 24, 2007Hi,
Can anyone tell me how to pass parameters from one exec sql task to other ?... (I used stored proc in 1st exec sql task) and passed input parameter (default value set using a variable A) and stored the output parameter value in another variable B.
In the 2nd exec sql task , I passed the output param ( value of B) and doing insert into table xyz...
I get errors (in passing int and string values) . I tried using ole-db as well as ado.net.
Kindly give sample example.
Thanks,
Exec Stored Procedure Permission
Feb 12, 2008Hello,
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Thanks
Arun
Passing Variables To EXEC And Stored Prodecure?
Nov 7, 2007In this line, @BaseName varchar(50) is polulated by a cursor that queries a table for names of other databases. In this first example it works as predicted:
EXEC('SELECT COUNT (IdPartition) FROM '+@BaseName+'..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentity WHERE [IdPartition] = 0 AND StoreIdentifier IS NULL')
If I create this as an SP (I want the output into another table)
CREATE PROCEDURE GetPArtitionItems @BaseName varchar(50),@IdPartition int, @PartitionItems int OUTPUT
AS
SELECT COUNT (IdPartition) FROM ['+@BaseName+']..SAVESETSS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = @IdPartition AND StoreIdentifier IS NULL
GODeclare @PartitionItems intEXECUTE GetPartitionItems 'evmailboxstore1',0,@PartitionItems OUTPUT --EvMailboxStore1 is another table in the same database.
I get: Server: Msg 208, Level 16, State 1, Procedure GetPArtitionItems, Line 7Invalid object name ''+@BaseName+'..SAVESET'
In this case the value is not passed into the @baseName-variable. What do I do wrong?
Thanks in advance - Tim Kuhnell
Exec A Stored Procedure And Not Wait For Response
Sep 20, 2005Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?
View 1 Replies View RelatedExecute Stored Procedure (with Parameters) With An Exec Command
Feb 21, 2004Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
How To Capture The ResultSet Of EXEC Command In Stored Procedure
Oct 1, 2007
Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.
Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.
--Israr
Report Error Using Stored Procedure With Exec Sp_executesql
Feb 13, 2008Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure
Feb 8, 2008Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
Transact SQL :: Using EXEC To Execute A Formula Stored In A String
May 31, 2005Basically, I have a table with a column that stores mathematical formulas in string format. When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables.
Look at the example below:
Suppose I have a string named @vcFormula that contains the following:"@dVar1 + @dVar2 / @dVar2"Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5. I can use the REPLACE function to change my original string to look like this:"1.0 + 2.5 / 2.5"
Now I want to execute this string and find the numeric result, placing it in a variable named @dResult. The following works, but presents a problem:CREATE TABLE #Result (dResult decimal(20, 10))INSERT #Result EXEC('SELECT ' + @vcFormula)SELECT @dResult = dResult FROM #ResultThe problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's.
So I attempted to change the temporary table above into an instance of the TABLE data type. This didn't work either because EXEC cannot be used to populate instances of the TABLE data type. Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's. Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC?
EXEC Stored Procedure For Every Line Of SELECT Result Table - How?
Jul 23, 2005Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies View RelatedIn Stored Procedure How To Loop Through Rows In Table And Pass Parameter To EXEC SP
Apr 26, 2008I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies View RelatedExec SQL Task: Capture Return Code Of Stored Proc Not Working
May 19, 2006I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
May 14, 2008Hello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.