Exec Sproc (sprocname Store In The Table)
May 22, 2008
Hi,
I'm trying to capture the value returned from sprocs. I stored the sproc name in the table and use cursor to run each sproc. Now the question is how can I capture and store the return value in a variable?
Here is the scenario:
Table1 has 1 column varchar(50) called vchsprocname
count_A -- procedure, select count(*) from ...
count_B -- procedure, select count(*) from ...
count_C -- procedure, select count(*) from ...
here is my query:
----------------------------------------------------
DECLARE @vchsprocname varchar(50)
DECLARE @count int
DECLARE cur CURSOR FOR
SELECT vchsprocname from table1
OPEN cur
FETCH NEXT FROM cur
into @vchsprocname
WHILE @@FETCH_STATUS = 0
BEGIN
exec @count = @vchsprocname -- I know I cannot do this, the vchsprocname cannot be variable. What else can I do?
FETCH NEXT FROM cur
into @vchsprocname
END
--------------------------------------------------
View 7 Replies
ADVERTISEMENT
Apr 7, 2013
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
View 17 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
May 14, 2001
Hello,
I need to get the count into a local variable:
Select @SQL = 'Select ' + @TotalRowCount + ' = Count(*) )' + ' From ' + @TableName + ' Where ' + @WhereClause
Exec(@SQL)
It complains about ‘…. Integer…’, but even if I use a varchar parm and convert Count to varchar in the sql statement, it still does not work. It does not like the = , or so it says.
Any help greatly appreciated,
Judith
View 4 Replies
View Related
Feb 6, 2008
And no, not through EM or SSMS
I want to interogate the catalog, so I can hae a job execute and do the grants in case a developer forgets
I mean I guess I can do the all everytime, but I don't know what the impact would be. It appears to be none, because of already done that, but in DB2 that would be bad as it would keep adding rows to the system tables
I have this so far
select *
from syspermissions p
inner join sysusers u
on u.uid = p.grantee
inner join sysobjects o
on o.id = p.id
where u.name = 'mepuser'
and o.name not like 'dt_%'
order by p.id
The M$ catalog is a royal pain
View 8 Replies
View Related
Feb 5, 2004
Folks
Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity .....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.
update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID
FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.
Thanx
View 3 Replies
View Related
Aug 15, 2005
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
View 1 Replies
View Related
Nov 8, 2000
What command do you use to run a DTS package from a stored procedure....
The XP Copy is not working???
Thanks,
~Lee
View 1 Replies
View Related
May 26, 2004
Hello, can anyone offer any advice on this problem related to store procedures.
The following 2 chunks of SQL illustrate the problem
--1
declare @lsFilt varchar(16)
select @lsFilt = 'fil%ter'
select * from sysobjects where name like @lsFilt
--2
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''fil%ter'''
exec (@lsQuery)
When I view the execution plan the cost % breakdown is approx 82%, 18%. The second query does a bookmark lookup and an index seek while the first slow query does a clustered index seek and takes approx 5 times longer to do.
Now my real question is suppose I have an store procedure to run a similar query. Should be writing my SPs along the lines of
create proc SP2Style
@psFilter varchar(16)
AS
declare @lsQuery varchar(128)
select @lsQuery = 'select * from sysobjects where name like ''' @psFilter + ''''
exec (@lsQuery)
GO
instead of
create proc SP1Style
@psFilter varchar(16)
AS
select * from sysobjects where name like @psFilter
GO
Is there another way to write similar store procedures without using dynamic query building or the exec but keep the faster execution speed?
thanks
Paul
View 2 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Sep 21, 2005
How can I determine when a sproc or table was last used?
I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure??
Thanks,
DL
View 5 Replies
View Related
Dec 13, 2005
I have an application that uses Integrated Windows authentication. My Web.config looks like below
<add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" />
When users try to access my application, they get the below error:
Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo'
The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse.
Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO.
Thanks,
View 2 Replies
View Related
Aug 5, 2007
I have the following query in sql 2005:
PROCEDURE [dbo].[uspInsert_Blob] (
@fName varchar(60),
@fType char(5),
@fID numeric(18, 0),
@bID char(3),
@fPath nvarchar(60)
)
as
DECLARE @QUERY VARCHAR(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?
View 1 Replies
View Related
Jan 4, 2005
To all,
Here's the error:
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 159:
Line 160:objConn.Open()
Line 161:objCmd.ExecuteNonQuery()
Line 162:objConn.Close()
Line 163:
Source File: C:FullerAviationSupplymanagecatalog.vb Line: 161
Stack Trace:
[FormatException: Input string was not in a correct format.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
managecatalog.SubmitProd(Object s, EventArgs e) in C:FullerAviationSupplymanagecatalog.vb:161
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
Here's the code from my vb file:
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Protected WithEvents dgProductCatalog As datagrid
Protected WithEvents tbxProductName As TextBox
Protected WithEvents tbxInventory As TextBox
Protected WithEvents dgCatProd_Active As DropDownList
Protected WithEvents dlvendor As DropDownList
Protected WithEvents dlSubcat As DropDownList
Protected WithEvents btnLinkvend As Button
Protected WithEvents tbxshortdesc As TextBox
Protected WithEvents tbxlongdesc As TextBox
Protected WithEvents qtyPA As TextBox
Protected WithEvents pricePA As TextBox
Protected WithEvents qtyPB As TextBox
Protected WithEvents pricePB As TextBox
Protected WithEvents qtyPC As TextBox
Protected WithEvents pricePC As TextBox
Protected WithEvents sizeX As TextBox
Protected WithEvents dlX As DropDownList
Protected WithEvents sizeY As TextBox
Protected WithEvents dlY As DropDownList
Protected WithEvents sizeZ As TextBox
Protected WithEvents dlZ As DropDownList
Protected WithEvents weight As TextBox
Protected WithEvents dlweight As DropDownList
Protected WithEvents dgInsertProduct As Button
Protected WithEvents thumbfile As TextBox
Protected WithEvents mainfile As TextBox
Dim objDA As SqlDataAdapter
Dim objDS As New DataSet()
Dim objDX As New DataSet()
Dim objDV As New DataSet()
Dim objDU As New DAtaSet()
Dim objDW As New DAtaSet()
Dim objDT As DataTable
Dim objDR As DataRow
Dim objConn As SqlConnection = New SqlConnection(ConStr)
Sub Page_Load()
If Not IsPostBack Then
BindData()
End IF
End Sub
Sub BindData()
objDA = New SqlDataAdapter("GETCATALOG",objConn)
objDA.Fill(objDS, "MainCatalog")
objDT = objDS.Tables("Catalog")
dgProductCatalog.DataSource = objDS
dgProductCatalog.DataBind()
objDA = New SqlDataAdapter("GETSUBCAT",objConn)
objDA.Fill(objDX, "SubCat")
dlSubcat.DataSource = objDX
dlSubcat.DataValueField = "SubCat_ID"
dlSubcat.DataTextField = "SubCat_Name"
dlSubcat.DataBind()
objDA = New SqlDataAdapter("GETVENDOR",objConn)
objDA.Fill(objDV, "Vendor")
dlvendor.DataSource = objDV
dlvendor.DataValueField = "VendorID"
dlvendor.DataTextField = "CompanyName"
dlvendor.DataBind()
objDA = New SqlDataAdapter("GETSIZEUNITS",objConn)
objDA.Fill(objDU, "Units")
dlX.DataSource = objDU
dlX.DataValueField = "Size_ID"
dlX.DataTextField = "Size_Unit"
dlX.DataBind()
dlY.DataSource = objDU
dlY.DataValueField = "Size_ID"
dlY.DataTextField = "Size_Unit"
dlY.DataBind()
dlZ.DataSource = objDU
dlZ.DataValueField = "Size_ID"
dlZ.DataTextField = "Size_Unit"
dlZ.DataBind()
objDA = New SqlDataAdapter("GETWEIGHTUNITS",objConn)
objDA.Fill(objDW, "Units")
dlweight.DataSource = objDW
dlweight.DataValueField = "weight_unitID"
dlweight.DataTextField = "weight_Unit"
dlweight.DataBind()
End Sub
Sub SubmitProd(s As Object, e As EventArgs)
Dim objCmd As New SqlCommand("INSERTPRODUCT", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@prodName", tbxProductName.Text)
objCmd.Parameters.Add("@descShort", tbxshortdesc.Text)
objCmd.Parameters.Add("@descLong", tbxlongdesc.Text)
objCmd.Parameters.Add("@qtyPA", SqlDbType.int ).Value = qtyPA.Text
objCmd.Parameters.Add("@qtyPB", SqlDbType.int ).Value = qtyPB.Text
objCmd.Parameters.Add("@qtyPC", SqlDbType.int ).Value = qtyPC.Text
objCmd.Parameters.Add("@pricePA",SqlDbType.money).Value = pricePA.Text
objCmd.Parameters.Add("@pricePB",SqlDbType.money).Value = pricePB.Text
objCmd.Parameters.Add("@pricePC",SqlDbType.money).Value = pricePC.Text
objCmd.Parameters.Add("@vendorID", dlvendor.SelectedItem.Value)
objCmd.Parameters.Add("@inventoryAvail", SqlDbType.int).Value = tbxInventory.Text
objCmd.Parameters.Add("@imageThumb", thumbfile.Text)
objCmd.Parameters.Add("@imageLarge", mainfile.Text)
objCmd.Parameters.Add("@sizeX", sizeX.Text)
objCmd.Parameters.Add("@sizeY", sizeY.Text)
objCmd.Parameters.Add("@sizeZ", sizeZ.Text)
objCmd.Parameters.Add("@sizeUX", dlX.SelectedItem.Value)
objCmd.Parameters.Add("@sizeUY", dlY.SelectedItem.Value)
objCmd.Parameters.Add("@sizeUZ", dlZ.SelectedItem.Value)
objCmd.Parameters.Add("@weight", weight.Text)
objCmd.Parameters.Add("@prodActive", dgCatProd_Active.SelectedItem.Value)
objCmd.Parameters.Add("@weightU", dlweight.SelectedItem.Value)
objCmd.Parameters.Add("@SubCat_ID", dlSubcat.SelectedItem.Value)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
tbxProductName.Text=""
tbxInventory.Text=""
tbxshortdesc.Text=""
tbxlongdesc.Text=""
qtyPA.Text=""
pricePA.Text=""
qtyPB.Text=""
pricePB.Text=""
qtyPC.Text=""
sizeX.Text=""
sizeZ.Text=""
sizeZ.Text=""
weight.Text=""
BindData()
End Sub
And here's the Stored Procedure:
CREATE PROCEDURE INSERTPRODUCT
(
@prodName nvarchar(50),
@descShort nvarchar(3000),
@descLong nvarchar(4000),
@qtyPA int,
@qtyPB int,
@qtyPC int,
@pricePA money,
@pricePB money,
@pricePC money,
@vendorID int,
@inventoryAvail int,
@imageThumb nvarchar,
@imageLarge nvarchar,
@sizeX nvarchar,
@sizeY nvarchar,
@sizeZ nvarchar,
@sizeUX int,
@sizeUY int,
@sizeUZ int,
@weight nvarchar,
@prodActive int,
@weightU int,
@SubCat_ID int
)
AS
INSERT INTO ProductCatalog (ProductName, Desc_Short, Desc_Long, qty_PerA, qty_PerB, qty_PerC, Price_PerA, Price_PerB, Price_PerC, VendorID, InventoryAvail, image_Thumb, image_Large, Size_X, Size_Y, Size_Z, SizeUnit_X, SizeUnit_Y, SizeUnit_Z, weight, Prod_Active, weight_unit, SubCat_ID) VALUES (@prodName, @descShort, @descLong, @qtyPA, @qtyPB, @qtyPC, @pricePA, @pricePB, @pricePC, @vendorID, @inventoryAvail, @imageThumb, @imageLarge, @sizeX, @sizeY, @sizeZ, @sizeUX, @sizeUY, @sizeUZ, @weight, @prodActive, @weightU, @SubCat_ID)
GO
Any help would be greatly appreciated.
Regards,
Seth
View 2 Replies
View Related
May 2, 2005
Okay, here's the scenario....
I have a bunch of un-related tables that I need to make relational.
I have a bunch of SProcs set up to help with the relating of the tables but I need the quickest way to get the values from the tables and into the SProcs.
Currently, I'm using .NET to cycle through the necessary tables, sending in their values into the SProcs, one at a time.
I'd like to have some SQL-ized way to do this so I can just make another step in my DTS package that already copies over the un-related tables from DB2.
What I'd imagine the code to look like would be something like...
EXEC SP_EMPLOYEES (SELECT FIRST_NAME,LAST_NAME FROM oldEmployees)
I know, now, that it's not as easy as that but I'm thinking there MUST be a way - even if it requires creating a new SProc.
TIA
View 1 Replies
View Related
Apr 7, 2008
Hi,
I've got 2 tables
Table_A with 2 fields: ThreadID & Rating
This Table is updated as users rate forum threads.
Then I have Table_B which should display the some info in a Gridview
Table_B has 3 fields: Name, ID and Score
I want the Rating from Table_A to update to Table_B's Score Field.
I suppose I should write a sproc to do this, but I am not quite sure how;
This is what I would like the sproc to do...
Update Table_B
SET Score = Table_A.Rating WHERE ID = Table_A.ThreadID
Any help on this...or is there a should I not use a sproc.
Also...how do I get the sproc to update the table automatically (without me running the sproc manually)?
View 10 Replies
View Related
Oct 5, 2007
Hi. This is a SQL question.
I am trying to wrap a stored procedure with a UDF so I can do selects against the result. The following doesn't work, but is it possible to do something like:
Create Function test()returns @tmp table ( myfield int)asbegin insert into @tmp (field1) Exec dbo.MySprocWhichRequires3ParmsAndReturnsATable 5, 6, 10 output returnend
Thanks
View 1 Replies
View Related
Dec 12, 2007
My goal is to recreate a table daily so that the data is updated. This could be a bad decision performance-wise, but I felt this was simpler than running a daily update statement. I created a stored procedure:SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_CreatetblImprintPhrase
AS
DROP TABLE tblImprintPhrase
GO
CREATE TABLE tblImprintPhrase
(
CustID char(12),
CustName varchar(40),
TranNoRel char(15)
)
GO However, I was looking to edit the stored procedure, changing CREATE to ALTER, but when I do so, I am prompted with: Error 170: Line 2: Incorrect syntax near "(". If I change back to CREATE, the error goes away, but the sproc cannot be run because it already exists. Any thoughts?
View 2 Replies
View Related
Jun 10, 2008
I have a table with this structure
ID | Ticker
-------------------
1330 |AAB-Bank
1336 |AEGON
1367 |ALZSE
1420 |ASSGEN
2812 |AVLN
I have a sproc called usp_validTicker that will take 2 parameters: ticker and date. It will return the valid ticker for that date.
I like to have the sproc going through each ticker in the table and return the valid tickers.
For example
exec usp_validTicker 'AAB-Bank','2008-6-10' will return 'AAB' and my final table will be
ID | Ticker
-------------------
1330 |AAB
1336 |AEGON
1367 |ALZSE
1420 |ASSGEN
2812 |AVLN
View 13 Replies
View Related
Sep 21, 2005
Hi. It seems to be very simple, actually, but I don't know if it isfeasible in TSQL. I have a sproc which gathers in one place many callsto different other sprocs, all of them taking a 'StoreGroupe'parameter. I would like to add a case where if the call has NOStoreGroupe parameter, the sproc should LOOP thru all records in tableStoreGroupeTable, read the column StoreCode, and pass that value as aparam to the other sprocs, as in:CREATE PROCEDURE MySproc(@StoreGroupe nvarchar(6) = NULL)ASif (@StoreGroupe is not null)BeginExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............EndElseBeginA 'Group Code' has NOT been specifiedI want to take all the StoreGroups in tableStoreGroupeTable, in turn.I would like to do SOMETHING LIKE THIS:Do While not [StoreGroupeTable].EOFRead [Code] from [StoreGroupeTable]Set @StoreGroupe = The value I just readExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............LoopEndGOIs that feasible in a sproc, or do I have to do this in the client(ADO) ?Thanks a lot.Alex.
View 4 Replies
View Related
Jul 12, 2006
I need to call a sproc about 1000 times and build a table from all the results.
How do I write an insert statement that will take the recordsets from the sproc and put it into a temp table?
View 1 Replies
View Related
Sep 18, 2006
Hullo folks, I'm having what I assume is a fairly mundane security issue.
I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.
Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.
Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?
View 7 Replies
View Related
Apr 12, 2006
i am writing a sproc that calls another sproc. this 2nd sproc returns 3 or 4 rows. i want to be able to insert these rows into a table. this sproc is inside a cursor, as i have to call it many times.
how do i insert the rows that it returns into another table??
View 10 Replies
View Related
Aug 3, 2015
I need to output a sproc into a new physical table, so the column definitions match the output.
Select Into DbName.NewTableName
Followed by an
Insert Into DbName.NewTableName
From (SprocNameHere),
View 9 Replies
View Related
Mar 5, 2015
I'm trying to do something like this:
Loop through #Temp_1
-Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter
-Store result set of Sproc_ABC into #Temp_2
-Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ
End Loop
It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?
View 2 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Jul 10, 2007
I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this
declare @a table
(
employeeid int
)exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2
How can i do this? This is throwing an error.
Thanks.
View 8 Replies
View Related
Nov 3, 2006
Hi All,
How do I filter an EXEC table as to put the returned value into a variable?
I have to filter an EXEC table because I am using a table variable to define which table tto select.
Help appreciated.
View 6 Replies
View Related
Oct 11, 2004
Hi is there a way to store results of the query in a temp table?
Something like
select exec(sp_Run)
into #t1
Is this possible?
View 4 Replies
View Related
Jan 23, 2006
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
View 4 Replies
View Related
Dec 31, 2006
Hi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN
How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
Thanks a lot for guidelines
View 8 Replies
View Related
