How To Change View Code Or Table Form Stored Procedure + -
Jun 1, 2008
need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example
if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
Code Snippet
SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)
AS new_unit
FROM dbo.empList
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
TABLE dbo.empList
empid ShiftType unit_date unit
----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3
TNX for the help
View 7 Replies
ADVERTISEMENT
Aug 28, 2007
Hi guys
I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs must be ordered in certain way , for example "1st managers then engineers … workers … " so In the table that job titles are defined there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order .
The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that.
Any Idea?
View 8 Replies
View Related
Sep 7, 2005
I need help to create a stored procedure to get the table names from a database and create a Union view. There are over 100 tables.
View 3 Replies
View Related
Nov 14, 2007
I have three table and I have to fetch some data from each one. This can be done by calling three diffrent stored procedures for each one.But it can be done with view and joining these three tables and only one time calling this view and getting the same result.(These joins can be from diffrent database too)
Which one is better View and joining these three tables and call this view one time or calling three stored procedures in for example .net side.
Sincerely
Kianoosh
View 7 Replies
View Related
Oct 20, 2007
Hi all .
I am going to consult you about a security concept .
In my database I have a set of tables (eg. City , Country , ...)
and I have a corresponding View for each Table (eg. V_City , V_Country , ...)
and There are a set of Add/Delete/Update/List stored procedures for each object , the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security? Not that I access SQL Server Database from a .Net App.
Thanks in advance.
View 9 Replies
View Related
Jun 8, 2006
Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?
I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:
CREATE TABLE < t > FROM SELECT id, name FROM < w > ...
Thank you!
View 6 Replies
View Related
Jul 29, 2004
I want to display the data in datagrid using the stored procedure,
Can you please tell me, how i can create the stored procedure for the following:
using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)
I want to display the stored procedure data in my Datagrid.
Thank you very much for the help.
the following is complete inline code on my webform.
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
Dim objCmd As New SqlCommand
Dim dataAdapter As SqlDataAdapter
objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"
objConn.Open()
dataAdapter = New SqlDataAdapter
dataAdapter.TableMappings.Add("Table", "Orders")
dataAdapter.SelectCommand = objCmd
dataSet = New DataSet("Orders")
dataAdapter.Fill(dataSet)
dtgOrders.DataSource = dataSet
dtgOrders.DataBind()
objConn.Dispose()
View 1 Replies
View Related
Nov 13, 2006
i have created a stored prcedure but is always give error : and please check the ways is correct using stored procedure
"Procedure or Function 'Add_Cb_Entry' expects parameter '@Date', which was not supplied."
Dim SqlPrm As SqlParameter
Dim SqlCmd As New SqlCommand
With SqlCmd
.Connection = SqlConnection 'this is my connection setting
.CommandText = "Dbo.Add_Cb_Entry"
.CommandType = CommandType.StoredProcedure
End With
SqlPrm = SqlCmd.Parameters.Add("@Cashbook_Id", Nothing)
SqlPrm.Direction = ParameterDirection.Output
SqlPrm = SqlCmd.Parameters.Add("@Date", SqlDbType.DateTime, 8, TxtDate.Text)
SqlPrm.Direction = ParameterDirection.Input
SqlPrm = SqlCmd.Parameters.Add("@Entry_ID", SqlDbType.Int, 4, "4")
SqlPrm = SqlCmd.Parameters.Add("@Entry_Name", SqlDbType.VarChar, 75, "Irfan Imdad Memon")
SqlPrm = SqlCmd.Parameters.Add("@Description", SqlDbType.VarChar, 100, "Chk")
SqlPrm = SqlCmd.Parameters.Add("@Amount", SqlDbType.Money, 13, "1000.20")
SqlPrm = SqlCmd.Parameters.Add("@Type", SqlDbType.NChar, 2, "DB")
SqlPrm = SqlCmd.Parameters.Add("@Entry_Status", SqlDbType.VarChar, 20, "Account")
SqlPrm = SqlCmd.Parameters.Add("@Ref_No", SqlDbType.Int, 4, "1")
SqlPrm = SqlCmd.Parameters.Add("@Ref_Status", SqlDbType.VarChar, 20, "MeterialPurchase")
OpenConnection() 'this is my connection setting
SqlCmd.ExecuteNonQuery()
CloseConnection() 'this is my connection setting
TxtInvoiceNo.Text = SqlCmd.Parameters("@Cashbook_Id").Value
View 1 Replies
View Related
Aug 24, 2007
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
View 3 Replies
View Related
Feb 24, 2008
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
View 2 Replies
View Related
May 11, 2007
Hi frdz, I have created the following stored procedure in sql server 2005. In my database i have one option for the payment mode which can be done thru cash or credit(cheque). I have created my web-application in asp.net with C# 2005. There i have a dropdownlist box for the user to select the option whether wants to do the payment thru cash or cheque.Depending on that selection if user selects cheque then all the reqt for cheque like it's no,dt,bankname etc...are visible.but if user selects the option as cash then the cheque details become invisible.Depending on the selection of the user of the asp.net web-form how can i do changes in the stored procedure... i can write the condition likeif paymentmode=cash then ..........else.............but where and how can it be written ...pls tell methanxs in adv...u can go thru my below SP ALTER PROCEDURE MiscellaneousStoredProcedure
@miscid int output,
@storename varchar(20),--store name to storeid
@accountname varchar(20),
@groupname varchar(20),
@paymentdt datetime,
@payeename varchar(30),
@paymode varchar(20),
@bankname varchar(50),
@chqdt datetime,
@chqno varchar(20),
@amt numeric(10, 2),
@bal numeric(10, 2),
@remarks varchar(50)
as
declare
@storeid int,
@accountid int,
@groupid int
begin
set nocount on
select @miscid = isnull(max(@miscid),0) + 1 from miscellaneourpay
if exists (select * from storemaster where storename = @storename)
select @storeid = storeid from storemaster where storename = @storename
if exists (select * from accountmaster where accountname =@accountname)
select @accountid = accountid from accountmaster where accountname =@accountname
if exists (select * from accountgroupmaster where groupname=@groupname)
select @groupid=groupid from accountgroupmaster where groupname=@groupname
begin transaction
insert into miscellaneourpay
(
miscid,
storeid,
accountid,
groupid,
paymentdt,
paymode,
payeename,
bankname,
chqdt,
chqno,
amt,
bal,
remarks
)
values
(
@miscid,
@storeid,
@accountid,
@groupid,
@paymentdt,
@paymode,
@payeename,
@bankname,
@chqdt,
@chqno,
@amt,
@bal,
@remarks
)
commit transaction
end
View 2 Replies
View Related
May 15, 2008
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data.
There is a web app already created that has the followig code to get the data from the database
Dim strSQL ast string = "UsersSelectCommand"
intLoginID = objDataReader("LoginID")
My stored procedure is the following:
CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName
GO
The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
View 2 Replies
View Related
Nov 2, 2004
I have the following stored proc. which i am using on the front end to get all the record from table:
if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.
CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO
Thank you very much.
View 2 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Jan 16, 2004
I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.
And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?
View 4 Replies
View Related
Sep 1, 2005
Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas
View 4 Replies
View Related
Jul 2, 2004
I have the following stored procedure in sql server 2000:
(@nsn varchar 15 int,
@item_nam varchar 255 ouput
)
AS
declare @stockquantity int
select @stockquantity = stockquantity
from inventory
where stockquantity = 0
select @item_nam = item_nam
from inventory where nsn = @nsn
delete from inventory
where nsn = @nsn
and stockquantity = 0
GO
what would the vb.net code be to display the item_nam if the query was successful in deleting a record, but would then retutrn a different mesage if the item was not deleted becasuse the stockquantity was not equal to zero. Right now it returns the item_nam even if the record wsa not deleted.
ANy help is much appreciated.
View 1 Replies
View Related
Jul 23, 2005
I don't know if this is possible. However, what i am attempting to do isusing C#'s window forms. I open up an excell sheet stored in my windowsform. The excel sheet stores names of the stored procedures in thatdatabase. I want to know if it's possible to click on that storedprocedure to open up a link to display the code of that stored procedure ofcourse in a read only mode.any suggestions....
View 2 Replies
View Related
Sep 22, 2007
hi all,i need to convert these simple PHP code into stored procedure :<?php$result=mssql_query( "SELECT whid, whcode FROM warehouse" );while( $wh = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT plid, nopl FROM packlist WHERE whid ='" . $wh->whid . "'";while( $pl = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT qty FROM packlistnmat WHERE plid ='" . $pl->plid . "'";while( $pln = mssql_fetch_object( $result ) ){echo "Stock from " . $wh->whcode . " AND Packing List number " .$pl->plid . " = " . $pln->qty;}}}?>my focus is in nested query, then i can call each field from the query(SELECT whid, whcode...) in sub query.thanks,aCe
View 4 Replies
View Related
Jan 8, 2008
help BUG in my sql code
i can not see this employee 111111 whan i run this code
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
TNX
Code Block
------------------------
-- create mod cycle shift pattern
------------------------
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
-- select * from @shifts_pattern
--- end shift pattern
--------------------------------
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
SELECT 222222,2,'29/01/2008','30/01/2008' UNION ALL
SELECT 333333,3 ,'27/01/2008','30/01/2008' UNION ALL
SELECT 444444,5 ,'26/01/2008','30/01/2008'
--------------------------------
-- create shifts table
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
--SET
----@StartDate = StartDate
---- last day of next month
--SET
--@EndDate = EndDate
--@StartDate = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1 )
---- last day of next month
--SET
--@EndDate = DATEADD(m ,3,GETDATE()-DAY (GETDATE()) + 1)- 1
--set
--@current = @StartDate
----
----------------- open list table for emp with curser
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
View 1 Replies
View Related
Nov 3, 2006
I need to have my middle tier code get the T-SQL code for a stored procedure. So, I need to create a string something like:
string tsqlCode = @"CREATE PROCEDURE "NSInsertGenerator"
@GeneratorName NVARCHAR(255),
@SystemName NVARCHAR(255),
@ThreadPoolSize INT,
@ActivationState TINYINT
AS
INSERT INTO "NSGenerators"
( GeneratorName, SystemName, ThreadPoolSize, ActivationState)
VALUES(@GeneratorName, @SystemName, @ThreadPoolSize, @ActivationState)
GO"
This string will be analyzed by the middle tier code so that the list of parameters for this procedure will be built dynamically depending on the current version of this stored procedure.
We are using SQL Server 2000 SP4.
Could someone recommend me how I can get T-SQL code of a stored procedure, please?
Thanks a lot!
View 4 Replies
View Related
Apr 3, 2007
Pls check my code for the stored procedure which i created for the companydetails including companyid P.K. Not Null int(4),companyname Not Null varchar (20),address varchar(30) where companyid is the primary key and it should be autogenerate.I also want that it should check if the name exits or not.It should also check that the field is entered and not kept null.If it's null then should return the error message.I want to write the queries select,insert,update and delete in the single stored procedure.How can i differ all the query individually in a stored procedure.The select and insert query are done on the button click event whereas the update,delete queries are performed in the gridview link event. Pls help me and modify my code accordingly with ur suggestions who know
the stored procedure very well.First read what i want then give reply.waiting for the reply and with corrections.The coding is perfomed in sql server 2005 and asp.net with C# 2005, 1 ALTER PROCEDURE CompanyStoredProcedure2 @uspcompanyid int,3 @uspcompanyname varchar(20),4 @uspaddress1 varchar(30), 5 @frmErrorMessage as varchar(256) OUTPUT,6 @RETURNVALUE as int OUTPUT,7 @RETURNID as int OUTPUT8 AS9 declare
10 @companyid int,11 @companyname varchar(20),12 @address1 varchar(30) 13 14 BEGIN15 16 begin17 Select @RETURNVALUE = -918 RETURN -919 end20 21 begin22 Select @frmErrorMessage = 'The Operation Mode Has Not Been Specified'
23 return -924 end25 26 27 28 begin 29 --validation...
30 if (@uspcompanyname is Null or @uspcompanyname = '')31 begin32 Select @RETURNVALUE = -933 select @frmErrorMessage = 'Company Name is empty'
34 return -935 end 36
37 if exists (select companyid from companymaster 38 where upper(companyname) = upper(cast(@uspcompanyname as varchar(20))))39 begin40 select @companyid = companyid from companymaster 41 where upper(companyname)=upper(cast(@uspcompanyname as varchar(20) ) )42 end43 else 44 45 select @companyname= cast (@uspcompanynameas varchar(20))46 select @address1= cast(@uspaddress1 as varchar(30))47 select @companyid = isnull(max(companyid),0) + 1 from companymaster48 49 IF exists(SELECT * from companymaster where companyname=@companyname)50 begin51 Select @frmErrorMessage = 'Record With Company Name ' 52 + @companyname + ' is Already Exisiting For The Company Name ' 53 return -954 end 55 56 -- the following codes inserts
57 begin transaction58 INSERT INTO companymaster59 ( companyname, address1)60 VALUES (@companyname,@address1)61 commit transaction62 63 select @RETURNVALUE = 064 select @RETURNID = @companyid65 66 end67 68 69 -- the following codes edit/updates
70 begin71 UPDATE companymaster 72 SET companyname=@companyname,73 address1=@address1 74 WHERE companyid =cast(@uspcompanyid as int)75 76 select @RETURNVALUE = 077 select @RETURNID = cast(@uspcompanyid as int)78 end79 -- the following codes delete
80 begin81 DELETE companymaster WHERE (companyid = @companyid)82 end 83 84 END 85
Pls help me and modify my code accordingly with ur suggestions who know the stored procedure very well.First read what i want then give reply.
View 4 Replies
View Related
Dec 23, 2007
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies
View Related
Jul 12, 2007
Hi,
I am using vs2005, vc++, sql2005 and trying to write a complex extended stored procedure.
It seems I must use switch clrafe for my executable to deploy to sql.
But with clrafe, I can no longer use such c code tools such as typedef structures and pointer manipulation!
But I need to do these things to translate a contiuous block of binary data into a series of field/value pairs to be written to the sql server!!! Doing so externally would mean a severe drop in throughput: going back to the server for each field instead of all as one block!
Is there a technique, or pragma command to allow "unmanaged" c code to reside in a sql extended stored procedure?
View 15 Replies
View Related
Apr 14, 2008
Hi,
I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.
I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)
I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.
Here is my SP
ALTER PROCEDURE uspSelectBarItemID2
(
@BarTabID INT,
@DrinkID INT,
@ReturnBarItemID INT OUTPUT
)
AS
BEGIN
SELECT @ReturnBarItemID = barItemID
FROM [Bar Items]
WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)
END
In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.
What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.
Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.
Cheers.
View 11 Replies
View Related
Apr 24, 2001
Hi,
I have a schedule task which call one of my stored procedure,
In this stored procedure, I need to change db owner of one of
database, but I find sp_changedbowner do not allow me to specify
db name,it only change current db,so I have to open a db before
call sp_changedbowner,but it is invalid..
CREATE PROC demo
as
begin
...
use demo_db //it is invalid
exec sp_changedbowner 'scott'
end
Can anyone give me ideas?
Thanks
View 1 Replies
View Related
May 18, 2001
Hello. I am trying to change the owner of a stored procedure from a user ID to dbo. How do i do this. I have tried sp_changeobjectowner but I get an error message. Can I do this from Enterprise Manager? btw, I am using SQL 7.0. Any help would be appreciated. Thanx
View 2 Replies
View Related
Dec 14, 2005
Can someone explain to me why the following doesn't work?declare @oname sysnameselect @oname=name from sysobjects where name like"df__mytable__mycol%"alter table mytable drop constraint @oname
View 4 Replies
View Related
Apr 5, 2007
I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views. Does anyone know why someone would do this? I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?
View 1 Replies
View Related
Jul 7, 2000
Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.
View 3 Replies
View Related
Jan 23, 2006
I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?
View 3 Replies
View Related
Mar 14, 2007
Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...
with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?
CallID DateStopTimeStopCallStatus
002161772006-01-2005:39:24Open
002161772006-01-2005:39:27Open
002161772006-01-2005:40:13Open
002161772006-01-2005:40:24Pending
002161772006-02-0716:05:47Pending
002161772006-02-2117:26:22Pending
002161772006-02-2117:29:06Pending
002161772006-02-2117:29:08Open
002161772006-03-0316:35:10Open
002161772006-04-0515:12:26Open
002161772006-04-0515:17:09Open
002161772006-04-1414:37:49Open
002161772006-04-1414:37:54Awaiting
002161772006-04-1911:20:30Awaiting
002161772006-04-1912:12:34Awaiting
002161772006-04-1912:12:37Awaiting
002161772006-04-1912:12:58Awaiting
002161772006-04-1912:13:00Closed[/b]
View 20 Replies
View Related
Sep 25, 2007
How can I create a stored procedure that combines the results from three views, and puts them in a temp table?
View 20 Replies
View Related
