Easy Question, How Do I Put A Paramater In Stored Proc, With A Memory Table

Apr 11, 2008

All i want to do is add a parameter for Territory_code how do i do that, in a stored procedure that uses a memory table.
the yellow shows the two places i thought i should be able to include it. Any help will be greatly appreciated!! thanks!
I couldnt post the whole code, so i deleted alot , just need to know where to put the param!




Code Snippet

USE [RC_STAT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PROC_RPT_Breeder_Sales]
AS (@Territory varchar(20)
BEGIN
SET NOCOUNT ON;

Declare @ReportingTbl TABLE (
Source_Id int,
Territory_Code varchar(20),
Territory_Description varchar (30),
Sort_Id int,
Column_Text varchar(20),
Subbrand_Key int,
SubBrand_Description varchar (30),
Period_1 Decimal(18,0),
Period_2 Decimal(18,0),
Period_3 Decimal(18,0),
Period_4 Decimal(18,0),
Period_5 Decimal(18,0),
Period_6 Decimal(18,0),
Period_7 Decimal(18,0),
Period_8 Decimal(18,0),
Period_9 Decimal(18,0),
Period_10 Decimal(18,0),
Period_11 Decimal(18,0),
Period_12 Decimal(18,0),
Period_13 Decimal(18,0),
YTD Decimal (18,0),
Total_Amount decimal (18,0))
------------------------------------------------------------------------------------------------------------------------------------------
----Distributor Load Section
------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO @ReportingTbl
(Source_Id,
Territory_Code,
Territory_Description,
Sort_Id,
Column_Text,
Subbrand_Key,
SubBrand_Description,
Period_1,
Period_2,
Period_3,
Period_4,
Period_5,
Period_6,
Period_7,
Period_8,
Period_9,
Period_10,
Period_11,
Period_12,
Period_13,
YTD,
Total_Amount)
SELECT
1 as Source_Id,
Tbv_Customer.Breeder_Territory_Code,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name,
CASE WHEN cusSales.Customer_Sales_Summary_Year = YEAR(GETDATE()) THEN 2 ELSE 1 END as Sort_Id,
cusSales.Customer_Sales_Summary_Year Column_Text,
cusSales.Sub_Brand_Id ,
Qry_Report_Level_Brand.Sub_Brand_Description,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_1,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END)AS Period_10,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12,
SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13,
0 as YTD,
SUM (cusSales.Customer_Sales_Summary_Amount) As Total
FROM RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension
INNER JOIN Tbv_Customer ON RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code = Tbv_Customer.Breeder_Territory_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales
ON Tbv_Customer.Customer_Code = cusSales.Customer_Code
INNER JOIN Qry_Report_Level_Brand
ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id
WHERE
cusSales.Bill_Customer_Code NOT IN ('RNPROC','RNPROF')
AND cusSales.Sub_Brand_Id <> 65 AND cusSales.Report_Level_Id = 85
AND cusSales.Report_Level_Id = 85
AND cusSales.Consolidated_Sales_Tables_Id = 6
AND cusSales.Customer_Sales_Summary_Year >= YEAR(GETDATE()) - 1
GROUP BY
Tbv_Customer.Breeder_Territory_Code,
RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Name,
cusSales.Customer_Sales_Summary_Year,
cusSales.Sub_Brand_Id ,
Qry_Report_Level_Brand.Sub_Brand_Description


SELECT
Source_Id,
Territory_Code,
Territory_Description,
Sort_Id,
Column_Text,
Subbrand_Key,
SubBrand_Description,
Period_1,
Period_2,
Period_3,
Period_4,
Period_5,
Period_6,
Period_7,
Period_8,
Period_9,
Period_10,
Period_11,
Period_12,
Period_13,
YTD,
Total_Amount
FROM @ReportingTbl
-- ORDER BY Source_Id, Sort_Id
where Territory_Code=@Territory
END





View 6 Replies


ADVERTISEMENT

Really Easy Stored Proc Question

Jan 20, 2004

I suppose it's not possible to return a varchar value from a stored proc? I keep getting a conversion error.

How can I return a varchar value, then?

Thanks.. sorry for the stupid question. I did some searching on Google and didn't find much.

View 4 Replies View Related

How To Pass Table Name As Paramater In Stored Procedure

Feb 5, 2008

I am using SQL2005 and would like to know if it is possible to pass a table name as an input parameter in a stored procedure?? I am struggling to get it to work.

For example:

CREATE PROCEDURE dbo.StartBlock

@TableName varchar(30),

@minX float OUTPUT,



AS



BEGIN

SET NOCOUNT ON;





SET @minX = (SELECT min(X) as mX

FROM @TableName);



END

GO

The above does not work and give the following error

Must declare the table variable "@TableName"

Thanks
Christie

View 5 Replies View Related

I Am Passing A Stored Procedure A Paramater...how Do I

May 22, 2000

make it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.

Any help is GREATLY appreciated. Thanks.

Robert

View 1 Replies View Related

Stored Procedure Sebquery As A Paramater

Jul 23, 2005

Ok,This sounds dangerous (and yes I know it is)But its in a controlled enviroment and I really need to know how to dothis.How can I pass a Subquery for an Exist or In clause as a paramaterSomething like thisCREATE procedure dbo.mytry@funk varchar(1000)asSelect * from Customers where exists(@funk)GOSo I would execute something like soexec mytry @funk='Select ID From Customers where ID < 100'Any Ideas, I have tried LOTS of things but I can actually get it towork.I need to use it conjunction with a 3rd party product that can onlyselect from a Stored Procedure, and I can only pass paramaters to theSP.Any suggestions ?ThanksChris

View 6 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Stored Proc Not Finding Table

Jul 17, 2005

Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db.  This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine.  It finds the table ok.  But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec
@fldNum    INT  ASBEGIN DECLARE @errCode   INT
 DELETE FROM tblServRec WHERE fldNum = @fldNum     SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
 SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath

View 4 Replies View Related

Temp Table Stored Proc

Mar 29, 2006

I take the information below in query analyzer and everything runs fine-returns 48 rows. I try to run it as a stored proc and I get no records. If I put a simple select statement into the stored proc, it runs, so it's not permissions. Can anyone help me with why this won't execute as a stored procedure? Articles seem to indicate you can do this with temp tables in a stored procedure. Thanks

declare
@style as int,
@disc as int,
@key as varChar(500),
@sdate as varChar(15),
@edate as varChar(15),
@ld as varChar(15)

set @style=0
set @disc=0
set @sdate='3/1/2006'
set @ld='2'

create table #ListAll (wid int, parentID int, myFlag int)

insert into #ListAll
SELECT top 100 percent wid, parentID, 0 as myFlag FROM myTable WHERE (@style=0 or styleID=@style)
and (@edate is null or start_date < @edate)
and ((start_date is null) or (datediff(day,start_date,@sdate) <1))
and (@ld='9' or charIndex(convert(varchar(1),datepart(dw,start_dat e)),@ld)>0)
and wid in (select wid from myTable2 where (@disc=0 or discID=@disc))
and wid in (select wid from myTable where @key is null or ([title] like '%' + @key + '%' or [keywords] like '%' + @key + '%'))

update #ListAll set myFlag=1 where parentID<>0
insert into #ListAll
select w.wid, w.parentID, 0 as myFlag from myTable w right join #ListAll on #ListAll.parentID=w.wid where #ListAll.parentID<>0
delete #ListAll where myFlag=1

SELECT top 100 percent srt, w.WID, w.parentID, w.[title], w.start_date, w.end_date, w.cancelled, w.url, styleID, w.[keywords], w.onlineID, w.httplocation, datepart(dw,w.start_date) as lddate
FROM myTable w
right join #ListAll on #ListAll.wid=w.wid
ORDER BY srt, start_date, [title]

drop table #ListAll
GO

View 4 Replies View Related

Output Of A Stored Proc Into A Table

Sep 2, 2004

Want to obtain the outpur of a xp_cmdshell (or any other procedure call) command to a table. Is it possible ?

View 6 Replies View Related

Stored Proc Parameter For Table Name

Sep 29, 2004

Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc

CREATE PROCEDURE [dbo].[sp_Update]
@DistributorID int,
@TableName varchar(50)
AS
UPDATE C
SET C.UnitCost = T.[Price]
FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code)
GO

NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.

Is it possible to use a parameter as a table name? OR is there another way to do this?

Thanks in advance for your help!

View 3 Replies View Related

How To Search For A Table Name In Each Stored Proc

May 22, 2008

Hi All,

We have modified a column name for a table. Now we need to find out all the database objects (stored procedures, functions, views) which access this table so that we can modify them. This is a very big database and its not easy to open the code for each object and check if its access that table.
To add to the complexity, its not just one table and column but a number of tables have been modified.

Any pointers is highly appreciated

Thanks,
Krishna.

View 3 Replies View Related

Populate Table With Stored Proc

Apr 24, 2007

I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off, for a fewweeks in advance. Anysuggestions?

View 4 Replies View Related

Populate A Table With Stored Proc.

Apr 25, 2007

I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off. Anysuggestions?

View 2 Replies View Related

Loop Thru A SQL Table In Stored Proc?

Jul 20, 2005

Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.

View 1 Replies View Related

Variables For Table Names In Stored Proc

Nov 8, 2004

i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:

Create Procedure VerifySubjectNum
(@forum_ID VARCHAR(10), @subject_num INT)
As
If Exists
(SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num)
Return 1
Else
Return 0

when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?

View 6 Replies View Related

(re)using A Temporary Table In A Stored Proc (was Confusion)

Feb 15, 2005

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?


Howdy!

View 14 Replies View Related

Temporary Table In 3 Diffirent Stored Proc

Mar 30, 2004

I know there maybe something similar of what im asking for but i just cant find it.

I have 3 Stored procedure.

SPA - create a temporary table "sp_getListOfChildren"
SPB - insert the data into the temp table "sp_InsertCategoriesFound"
SPC - display the list of categories i found "sp_ListingAvailableCategories"

process:
SPA call SPB and SPC call SPA

my problem is in the SPC. it seems that the table doesnt exist anymore when i do a select but in the message tab of my sql analyser i can see that the table have some data before executing that store proc..

Invalid object name '#TblTempCat'. for my SPC !! ??? why.. how do i detect a temp table in diffirent stored procedure per user and as to be temp table.. for multiple access.. "WEB"

============MY "SPC" CODE=============
alter PROCEDURE sp_ListingAvailableCategories @CurrentCategoryID AS uniqueidentifier
AS

exec sp_getListOfChildren @CurrentCategoryID

select * from #TblTempCat

select * from TblCategories where CatID not in (select CatID from #TblTempCat) and CatId <> @CurrentCategoryID

View 7 Replies View Related

Deleting All Records From Table W/stored Proc

Jan 27, 2006

Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected.

Current code(works)
Public Function DelAll()
MZKDB = MZKHRFin
If sloption = "L" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;"
ElseIf sloption = "S" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";"
End If
sqlConn.Open()
sqlTrans = sqlConn.BeginTransaction()
sqlCmd.Connection = sqlConn
sqlCmd.Transaction = sqlTrans
Try
sqlCmd.CommandText = sqlStr
sqlCmd.ExecuteNonQuery()
sqlTrans.Commit()
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf
SetCursor()
Catch e As Exception
Try
sqlTrans.Rollback()
Catch ex As SqlException
If Not sqlTrans.Connection Is Nothing Then
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf
SetCursor()
End If
End Try
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf
SetCursor()
Finally
sqlConn.Close()
End Try
ResetID()
End Function
If cbGenFY.Checked Then
sqlStr = "DELETE FROM FIN_FiscalYear"
TableName = "dbo.FIN_FiscalYear"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFY()
timeStepStop = Date.Now

DispOneCounts()
End If
If cbGenFund.Checked Then
sqlStr = "DELETE FROM FIN_Fund"
TableName = "dbo.FIN_Fund"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFund()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenFunc.Checked Then
sqlStr = "DELETE FROM FIN_Function"
TableName = "dbo.FIN_Function"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFunc()
timeStepStop = Date.Now
DispOneCounts()

End If
If cbGenObject.Checked Then
sqlStr = "DELETE FROM FIN_Object"
TableName = "dbo.FIN_Object"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenObject()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenCenter.Checked Then
sqlStr = "DELETE FROM FIN_Center"
TableName = "dbo.FIN_Center"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenCenter()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenProject.Checked Then
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "dbo.FIN_CodeBook"
DelAll()
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "dbo.FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "dbo.FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_Project"
TableName = "dbo.FIN_Project"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProject()
timeStepStop = Date.Now
TableName = "dbo.FIN_Project"
DispOneCounts()
End If
If cbGenProgram.Checked Then
sqlStr = "DELETE FROM FIN_Program"
TableName = "dbo.FIN_Program"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProgram()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenGL.Checked Then
sqlStr = "DELETE FROM FIN_gl"
TableName = "FIN_gl"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenGL()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenRevenue.Checked Then
sqlStr = "DELETE FROM FIN_Revenue"
TableName = "FIN_Revenue"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenRevenue()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenBank.Checked Then
sqlStr = "DELETE FROM FIN_VendorBankAccnt"
TableName = "dbo.FIN_VendorBankAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_VendorBank"
TableName = "dbo.FIN_VendorBank"
DelAll()
sqlStr = "DELETE FROM FIN_bankAdd"
TableName = "dbo.FIN_bankAdd"
DelAll()
sqlStr = "DELETE FROM FIN_bankTERMS"
TableName = "dbo.FIN_bankTerms"
DelAll()
sqlStr = "DELETE FROM FIN_bank"
TableName = "dbo.FIN_bank"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenBank()
timeStepStop = Date.Now
TableName2 = "dbo.FIN_bankTERMS"
TableName3 = "dbo.FIN_BankAdd"
TableName4 = "dbo.FIN_VendorBank"
TableName5 = "dbo.FIN_VendorBankAccnt"
DispTwoCounts()
End If
If cbFinAP.Checked Then
sqlStr = "DELETE FROM FIN_Period"
TableName = "FIN_Period"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenPeriod()
timeStepStop = Date.Now
DispOneCounts()
End If

If cbFinVM.Checked Then
sqlStr = "DELETE FROM FIN_vendorClass"
TableName = "FIN_vendorClass"
DelAll()
sqlStr = "DELETE FROM FIN_vendorAdd"
TableName = "FIN_vendorAdd"
DelAll()
sqlStr = "DELETE FROM FIN_vendor"
TableName = "FIN_vendor"
DelAll()
sqlStr = "DELETE FROM FIN_AddressType"
TableName = "FIN_AddressType"
DelAll()
sqlStr = "DELETE FROM FIN_VendorStatus"
TableName = "FIN_VendorStatus"
DelAll()
sqlStr = "DELETE FROM States"
TableName = "States"
DelAll()
sqlStr = "DELETE FROM Country"
TableName = "Country"
sqlStr = "DELETE FROM FIN_IndustrialCodes"
TableName = "FIN_IndustrialCodes"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenIndCodes()
timeStepStop = Date.Now
DispOneCounts()
DelAll()
ClearCounts()
timeStepStart = Date.Now
FinVendStat()
timeStepStop = Date.Now
TableName = "FIN_VendorStatus"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinAddrType()
timeStepStop = Date.Now
TableName = "FIN_AddressType"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenCountry()
timeStepStop = Date.Now
TableName = "Country"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenState()
timeStepStop = Date.Now
TableName = "States"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinVM()
timeStepStop = Date.Now
TableName = "FIN_Vendor"
TableName2 = "FIN_VendorAdd"
DispTwoCounts()
End If
If cbFinbudget.Checked Then
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "FIN_CodeBook"
DelAll()
ClearCounts()
TableName = "FIN_Budget"
timeStepStart = Date.Now
FinBudget()
timeStepStop = Date.Now
DispOneCounts()
ClearCounts()
TableName = "FIN_Codebook"
TableName2 = "FIN_budgetAccnt"
timeStepStart = Date.Now
FinCodeBook()
timeStepStop = Date.Now
DispTwoCounts()
ClearCounts()
End If

View 4 Replies View Related

Using Same Table Variable In Child Stored Proc

Feb 5, 2008

Hi
I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs)
now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error

Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149
Must declare the table variable "@tmp_get_imu_retn".

Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.


Thanks,
BPG

View 11 Replies View Related

Stored Proc For Creating And Inserting Into A Table

Sep 18, 2006

I am having some trouble populating a table with values from other tables:

I am creating the stored proc as follows:

CREATE PROCEDURE make_temp_stat (@from datetime, @to datetime)

AS

DROP TABLE tempTable

Create tempTable

(

NumApplications (int),

NumStudents (int),

NumTeachers (int)

)

//Then I insert the values into the table as follows

INSERT INTO tempTable (NumApplications) SELECT Count(*) FROM [dbo].[CASE_APPLICATION] WHERE (OPEN_DT>= @from AND OPEN_DT <= @to)

INSERT INTO tempTable (NumStudents) SELECT Count(*) FROM [dbo].[CASE_STUDENTS] WHERE (APP_DT>= @from_dt AND APP_DT<= @to_dt)

INSERT INTO tempTable (NumTeachers) SELECT Count(*) FROM [dbo].[CASE_TEACHER] WHERE (JOIN_DT>=@from_dt AND JOIN_DT<= @to_dt)

GO

Nothing happens when I run this stored proc. Can sombody point out what exactly is wrong over here?

View 7 Replies View Related

Inserting Data Into A Table Using A Stored Proc

Apr 17, 2008



Hi All,
I want to insert data using a stored proc. Can anyone tell me the correct syntax for inserting data into a table using a stored proc?
Thanks

View 6 Replies View Related

I Am Trying To Use A Stored Proc To Page Thru Table But It Is Saying Incorrect Syntax Near GO

Aug 24, 2007

Can anyone helpCREATE PROCEDURE PagedResults_New
(@startRowIndex int,
@maximumRows int
)
AS
 
--Create a table variable
DECLARE @TempItems TABLE
(ID int IDENTITY,
ShortListId int
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO @TempItems (ShortListId)
SELECT Id
FROM shortlist SWHERE Publish = 'True' order by date DESC
 
-- Now, return the set of paged records
SELECT S.*, C.CategoryTitleFROM @TempItems t
INNER JOIN shortList S ON
t.ShortListId = S.Id
 WHERE ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
GO

View 1 Replies View Related

Passing Table Variable To Stored Proc / Function

Nov 6, 2002

Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View 3 Replies View Related

Can You Call A Stored Proc That Returns A Table Variable Using ADO?

Jan 8, 2004

I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."

It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?

View 1 Replies View Related

Storing A Stored-proc's Result Into A Temp Table

Jul 20, 2005

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 Related

Indexing Results Of Stored Proc (or New Table Created By One)

Jul 20, 2005

Hi,I am using data from multiple databases and/or queries. It would greatlysimplify and speed things up if I could use CONTAINS in processing theresults. However, "CONTAINS" requires the data to be indexed. Due to theamount of processing, I think it would be faster even if I had to re-indexevery time.For example, I would like to do something like this (simplified toillustrate the desired functionality... This should show all of the wordsfrom one table that are not contained in their current or inflectional formswithin another table):SELECT W1.ContentFROM(SELECT Word AS ContentFROM MyTable) W1LEFT OUTER JOIN(SELECT Phrase AS ContentFROM MyOtherTable) W2ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)WHERE W2.Content IS NULLCan the results of a procedure be indexed? If not, can I drop the resultsinto a new table and trigger an automatic index of it, pausing the procedureuntil the indexing is done?Or, it there another way?Thanks!

View 2 Replies View Related

Stored Proc To Copy Unnormalized To Normalized Table

Jul 20, 2005

I have a "source" table that is being populated by a DTS bulk importof a text file. I need to scrub the source table after the importstep by running appropriate stored proc(s) to copy the source data to2 normalized tables. The problem is that table "Companies" needs tobe populated first in order to generate the Identity ID and then usethat as the foreign key in the other table.Here is the DDL:CREATE TABLE [dbo].[OriginalList] ([FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Addr1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Companies] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[CompanyLocations] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[CompanyID] [int] NOT NULL ,[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThis is the stored proc I have at this time that does NOT work. Ituses the last Company insert for all the CompanyLocations which is notcorrect.CREATE PROCEDURE DataScrubSP ASBegin Transactioninsert Companies (Name) select Company from OriginalListIF @@Error <> 0GOTO ErrorHandlerdeclare @COID intselect @COID=@@identityinsert CompanyLocations (CompanyID, Addr1, City, State, Zip) select@COID, Addr1, City, State, Zip from OriginalListIF @@Error <> 0GOTO ErrorHandlerCOMMIT TRANSACTIONErrorHandler:IF @@TRANCOUNT > 0ROLLBACK TRANSACTIONRETURNGOThanks for any help.Alex.

View 3 Replies View Related

Stored Proc Call, 'table Name' As String, And T-sql Statement

Jun 16, 2006

I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?



TIA,

barkingdog



View 1 Replies View Related

TableAdapter Can't See Stored Proc Returned Fields When Using Temp Table

Apr 16, 2007

Hi,
Summary: When my stored procedure uses temporary tables then the TableAdapter won't be able to work out the field names and so won't work. I get an error in the TableAdapter configure wizard saying: Invalid object name '#TempTable'.
I'm not doing anything unusual so this must be a common problem. Let me explain:
I'm using Visual Studio 2005 and SQL Server 2000.
Detail: I've written a new stored procedure (SP)  that uses a temporary table in calculating the resulting results set (several fields with several rows). I recon the temporary table bit is significant.
I've created a new DataSet in VS2005 and dragged the stored proc onto the DataSet design surface.
I right click on the TableAdapter and enter the 'configure'. The problem is that the wizard doesn't think any fields are being returned by the SP.
If I try and do it another way I get the same problem: Right click on DataSet and add new TableAdapter (same thing happens, it won't recognise that there are fields being returned from the SP).
FYI: If I do it for an SP that doesn't use any temporary tables it all works like a dream (problem is that I need to use temporary tables as its complex   ).
Thanks for any advise

View 7 Replies View Related

Find Stored In Server Vide Proc For Given Table And Column

Jul 1, 2004

Hi,
I need to list out all the stored proc for all the DB in entire server.
My inpute parameter will be table name and column1/column2/column3...etc.
Please give me your input for my request.
Thank,
Ravi

View 2 Replies View Related

What Am I Missing Here? Using A Stored Proc Output To Build Table Rows

Nov 27, 2007

Hi all,

I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.

To continue with the set up, here is the stored procedure in question:
CREATE PROCEDURE [dbo].[HE_GetStks] AS

select top 15
Rating,
rank,
coname,
PriceClose,
pricechg,
DailyVol,
symbol
from

(selectf.rating,
f.rank,
s.coname,
cast ( f.priceclose as decimal(10,2)) as PriceClose,
cast ( f.pricechg as decimal(10,2)) as pricechg,
f.DailyVol,
f.symbol
from dailydata f, snames s
where f.tendcash = 0
and f.status = 1
and f.typ = 1
and f.osid = s.osid) tt
order by rating desc, rank desc

GO

The code in the calling bat file is:
REM *************************
REM BCP .WRK FILE
REM *************************
bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>

This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.

Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.

I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.

Any thoughts?

View 6 Replies View Related

SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR

Jul 10, 2014

I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

Here are my simulated procs...

Main Stored Procedure: This will be called once per each row of some table.

-- All this proc does is, prints out the list of parameters that are passed to it.

CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN

[Code] ....

Here is a sample call to the out proc...

EXEC dbo.MyOuterStoredProc @SessionID = 123

In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

View 9 Replies View Related

Execute Dynamic SQL Stored Proc Without Specifying Table Level Permissions

Sep 17, 2007

I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?

View 1 Replies View Related







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