Hi. Apologies if this has come up before, I wonder if someone can help.
I want to call a sproc from a trigger, passing in the value of a field from the updated/inserted/deleted row.
It would be like spUpdateUser @UserID = x
where x is the value from one of the columns of updated row.
What is the syntax for this please? TIA
I keep getting 0 returned, in both Query Analyzer and in my code. My double 'rate' just always ends up 0 or Null, and I've tried many different approaches. Here is the general idea. This is all for phone number searching.
I have a table with three columns - country, countrycode, and rate. The country code is two or three digits. In the code, I strip the first two digits of phoneNumber to make codeTwo and then strip the first three to make codeThree. That part works. I then pass codeTwo and codeThree to the sproc.
There are no overlapping codes (ie 23 and 237), so I search for a country code matching codeTwo and return that value. If none is found, then I search for a country code matching codeThree and return that value. If none is still found I return 0.
Here is the SQL 2000 stored procedure:
CREATE PROCEDURE getRate
(
@codeTwo int,
@codeThree int,
@rate int OUTPUT
)
AS
SELECT @rate = rate
FROM rates
WHERE countrycode = @codeTwo
IF @@rowcount = 0
SELECT @rate = rate
FROM rates
WHERE countrycode = @codeThree
RETURN@rate
GO
Here is the code calling the sproc:
string dbConn=ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection conn=new SqlConnection(dbConn);
SqlCommand cmd = new SqlCommand( "getRate", conn );
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parmTwoCountry = cmd.Parameters.Add("@codeTwo", SqlDbType.Int);
parmTwoCountry.Value = codeTwo;
SqlParameter parmThreeCountry = cmd.Parameters.Add("@codeThree", SqlDbType.Int);
parmThreeCountry.Value = codeThree;
SqlParameter parmRate = cmd.Parameters.Add("@rate", SqlDbType.Int);
parmRate.Direction = ParameterDirection.Output;
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
rate = Convert.ToDouble(parmRate.Value);
}
catch(Exception ex){throw new Exception(ex.Message);}
finally {}
Thanks for your time,
dave
It is possible to call WCF from SQL CLR, can I add a web reference or use the proxy created with svcutil ?
View 1 Replies View RelatedHi. 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
Hi, with SQL server 2005.
Could I write a SPROC with C# or VB.NET that calls methods within the .net library or
Could I package a .net methods into a SPROC to let other SPROC invoke it?
Thanks in advance.
Ricky.
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?
Hi
So, I know, after searching these forums, that it is possible to disable a trigger before updating a table and then enabling it again afterwords, for instance, from a stored procedure.
I might be dealing with hypotheticals here, but when I do a ...
ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
... from a stored procedure, will it not be database wide?
Should I worry about another change to the table happening in the timespan in which the trigger is disabled (which it would be for during a single update), which the trigger should have caught?
Regards, Egil.
I need some help calling a DLL or EXE from a SQL Trigger. I have the trigger set up, except I have no clue how to call a DLL or EXE or if it is even possible. Here is what I have for the Trigger so far:
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE TRIGGER CallProgIfParentID
ON Toc
FOR INSERT
AS
IF ((select ins.[ParentID] FROM inserted ins) = '57660')
-- I want to be able to programmatically change '57660' from an ASPX page
EXEC
-- This is where the call function goes
GO
Another question I have is can a trigger be created programmatically using ASPX pages written in VB.NET in VS2003?
Thank you in advance
i hav written a trigger in SQLEXPRESS DB but i dnt knw how to call in vb.net 2005...
View 2 Replies View RelatedHi I am trying to run a dll or exe from a procedure or trigger in the database.
I have tried EXEC master ..xp_cmdshell '\xx empdivConsoleApplication1.exe' and
use master exec dbo.StoredProcedure1
If I run the exe from explorer all is working, but from a trigger or procedure in the database nothing happens only executed successfully.
I loaded the assembly and procedure into the master database
Code Block
CREATE PROCEDURE [dbo].[ClassLibrary3] AS EXTERNAL NAME
[ClassLibrary3].[ClassLibrary3.Class1].[StoredProcedure1]
My C# code:
Code Block
public partial class StoredProcedures
{ [Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{ //------first a select to get some data String prosjekter = "";
String connString = @"Data Source=serverOFFICESERVERS;Initial Catalog=WSS_Content;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
SqlCommand sqlcmd = new SqlCommand("Select nvarchar3 prosjekt from dbo.AllUserData where tp_listId = @listid", conn);
sqlcmd.Parameters.AddWithValue("@listid", "xxx"); try { conn.Open(); SqlDataReader r = sqlcmd.ExecuteReader();
while (r.Read())
{ prosjekter += "" + r["prosjekt"] + "" + "";
}
r.Close(); conn.Close();
} catch (Exception)
{ conn.Close(); }
//-----write some changes back to a program pocketForm pf = new pocketForm();
try {
pf.LoadFromFile("C:/temp/div/xxx.xpf");
string xmlstring = pf.FormXML;
int id, item, items; id = xmlstring.IndexOf("xxxxx");
item = xmlstring.IndexOf("", id);
items = xmlstring.IndexOf("", item);
xmlstring = xmlstring.Remove(item, items - item);
xmlstring = xmlstring.Insert(item, prosjekter);
pf.FormXML = xmlstring;
pf.StatusModifiedLocally = true;
WebService.Login("http://xxxx.no", "xx", string.Empty, string.Empty);
WebService.Save(pf);
} catch (Exception)
{
}
What am I missing here ? Please answer this as I have tried everything with no success. Regards Ivar
Hi,
I have a VB program to sit somethere. I like to call that program with passing two parameters from a trigger on the SQL Server. The parameters are the contents of the table which the trigger is created on.
Is there any way to achieve that?
Thanks a lot!
Regards,
Kevin Jin
Is it possible to call an external web service from a SQL Server trigger or stored procedure?
View 6 Replies View RelatedIf an application makes a connection under a given user, performs the following:
- calls a stored procedure which performs an insert,
- the insert causes a trigger to be fired
- the trigger causes a sql statement to execute against a different
database
Questions:
* Does the user who called the procedure, also need permissions to this 2nd database ?
* What security context does this trigger get executed ?
I have a stored procedure that is being called by my .Net application. The user account that makes the connection has execute permission to the procedure. However, the procedure does an insert which causes a trigger to fire. This trigger does a sql statement against a table in a different database, which the user who is calling the procedure, does not have permissions to access. Im getting an exception when I call the procedure, and it basically says there was a permission error with the user accessing this other database.
I have created a trigger to call a program that is written by our program. The program is basically read the record in the table and write to a text file, then delete the record from the table.
The trigger is a after insert trigger. After we added the trigger, we insert a record to the table. The result is that the record still and did not get deleted. Also, the text file didn't get created either. It seems that it take a long time for the record to be written to the table.
But if we just run the program (a exe file), it can write a text file in the folder and delete the record. the trigger is basically:
USE [Zinter]
GO
/****** Object: Trigger [dbo].[ZinterProcess] Script Date: 04/29/2014 18:34:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
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
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
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
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
Hi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID.
What I have tried thus far:
UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID)
This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated.
Thanks!
Amanda
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something?
SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
hi all,
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @srch)
GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
prmSearch.ParameterName = "@srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")
My original string looks like this
strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"
What am I doing wrong?? Thanks in advance for any help.
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC:
___________________
CREATE PROCEDURE dbo.sp_addMembershipRole
@INCID Int
AS
declare @literal NVarChar (10)
SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role])
VALUES (@INCID, @literal)
GO
___________________
CODE:
___________________
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;
using System.Web.Security;
namespace admin
{
/// <summary>
/// Summary description for createMembership.
/// </summary>
public class createMembership : System.Web.UI.Page
{
protected System.Web.UI.WebControls.RequiredFieldValidator Vusername;
protected System.Web.UI.WebControls.RequiredFieldValidator Vpassword;
protected System.Web.UI.WebControls.RequiredFieldValidator Vretype;
protected System.Web.UI.WebControls.TextBox username;
protected System.Web.UI.WebControls.TextBox password;
protected System.Web.UI.WebControls.TextBox retype;
protected System.Web.UI.WebControls.Label lblError;
protected System.Web.UI.WebControls.Label lblDate;
protected System.Web.UI.WebControls.Button btnAdd;
private void Page_Load(object sender, System.EventArgs e)
{
if(! Page.IsPostBack)
{
string StrContactID = Request.QueryString["CID"].ToString();
Session["CID"]= StrContactID;
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public void btnAdd_Click(object sender, System.EventArgs e)
{
int intContactID;
string StrCID = Session["CID"].ToString();
intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text)
{
lblError.Text = "Retyping of your desired password did not match. Please try again.";
return;
}
string pwdSalt = CreateSalt(5);
string pwd = password.Text;
string pwdHash = CreatePasswordHash(pwd, pwdSalt);
string connStr= "server=****; uid=****; pwd=****; database=****";
string cmdStr= "sp_addMembershipUandP";
string cmd2Str= "sp_addMembershipRole";
SqlConnection CONN = new SqlConnection(connStr);
CONN.Open();
SqlCommand cmd = new SqlCommand(cmdStr, CONN);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommand cmd2= new SqlCommand(cmd2Str, CONN);
cmd2.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50);
param.Value = username.Text;
param = cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50);
param.Value = pwdHash;
param = cmd.Parameters.Add("@salt", SqlDbType.NVarChar, 50);
param.Value = pwdSalt;
param = cmd.Parameters.Add("@CID", SqlDbType.Int, 4);
param.Value = intContactID;
SqlParameter param2;
param2 = cmd2.Parameters.Add("@INCID", SqlDbType.Int, 4);
param2.Value = intContactID;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
CONN.Close();
return;
}
private static string CreateSalt(int size)
{
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] buff = new byte[size];
rng.GetBytes(buff);
return Convert.ToBase64String(buff);
}
private static string CreatePasswordHash(string pwd, string salt)
{
string saltAndPwd = String.Concat(pwd, salt);
string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "SHA1");
return hashedPwd;
}
}
}
create procedure
GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
declare @ZipID integer
declare @AddrID integer
set @AddrID=1
if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2)
return(@AddrID)
end
GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6))
AS
BEGIN
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
EXEC @CityID = dbo.GetCityID @City
EXEC @StateID = dbo.GetStateID @State
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
Return @ZipID
print @ZIPID
END
GO
Thx in advance
ha ha ,
havent used sql serv in years, and having trouble wit me sprocet! What devilish deed did i do?
Use Contacts
GO
CREATE PROC MaserIn
@entreddatetime= GETDATE(),
@initialsvarchar(2)=Null,
@Vendorvarchar(50),
@CkNovarchar(20),
@expTypevarchar(15),
@ckDatedatetime,
@ckAmtmoney,
@mIdintOUTPUT
AS
INSERT INTO [Contacts].[dbo].[Master](
[entered], [initials], [Vendor],
[CkNo], [expType], [ckDate], [ckAmt])
VALUES(@entered,
@initials,
@Vendor,
@CkNo,
@expType,
@ckDate,
@ckAmt)
SELECT @mID=@@iDENTITY
Hi.
I wanted to know if its possible to do this in a sproc.
if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.
If yes how can i do it..
Any help will be appreciated.
Regards
Karen
Hi, I am trying to Implement Multi parameter...
If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.
This is my sproc
ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]
@ClientId nvarchar(max)= NULL,
@StartDate datetime,
@EndDate datetime
AS
Declare @SQLTEXT nvarchar(max)
If @ClientId IS NULL
Begin
Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + @ClientId + ')
AND
o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + '
ORDER BY
o.OrderId DESC'
execute (@SQLTEXT)
END
any help will be appreciated.
Regards
Karen
hi all
i need to design a SPROC which will return me top n rows from a table.
like GetTopN 4, will give me top 4 tuples
/**
GetTopN
To list top n rows
Date - 05 July 2004
Yogesh Jangam
*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'GetTopN' AND type = 'P')
DROP PROCEDURE GetTopN
GO
/************************ Actual SP Code *********************************/
Create procedure GetTopN (@intN int)
AS
BEGIN
SET NOCOUNT ON
declare
SELECT TOP @intN from Employee -- this part i am not able to write
end
is there a answer?
Thanks
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
I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?
Here is my complete code:
USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP
GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE@ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE@CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
While @ListStr<>''
Begin
Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
If @CP<>0
Begin
Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
End
Else
Begin
Set @SV=Cast(@ListStr as varchar)
Set @ListStr=''
End
Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
Set adoConn = New ADODB.Connection
adoConn.Open connString
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
Set adoRS = .Execute
Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop
End With
Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub
Any ideas?
Thanks
I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.
I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.
I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?
Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.
Is it possible to do this with a sproc? Is this the right way to use a sproc?
Hi SQLersI'm in an unusual position of using a SQL box I'm not an admin of. To make it worse, it is SQL 2k. I haven't used this in anger for over a year. I don't even have the 2k tools installed and I'm just using SSMS.sp_adduser ain't working. I've nosed around in master and the reason why is somebody, for some reason, has created a sproc called (you guessed it) master.dbo.sp_adduser.I can't poke around as I would like and I don't have a 2k instance to bugger about with. Correct me if I am wrong but if there is some user defined master.dbo.sp_adduser sproc then there can't be a system sproc to add users? CREATE USER is SQL 2k5 too right? Users do get created by EM by the team managing the box so what command does this use? Anyone know or (flutters eyelashes) fancy running a profiler trace on EM to see?I want to convert my SQL 2k5 security objects script for SQL 2k. I can create the objects in SSMS so not critical."Overriding" system sprocs is not something I would ever do and I have no equivalent testing environment so any help highly appreciatedCheers
View 12 Replies View RelatedCREATE proc dbo.sp_address ( @Abbr char(2) )
as
DECLARE @StateID int
SET @Abbr = UPPER(ISNULL( @Abbr, '' ))
SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr )
set @StateID=53
IF ( @StateID is null )
INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr )
if @@ERROR = 0
SET @StateID = @@Identity
return(@StateID)
GO
Can I execute the above stored procedure in a function like this:
create function
sf_GetStateID( @Abbr char(2))
returns integer
begin
declare @StateID int
exec sp_address
return(@StateID)
end
I just want to cross check.
Tks