I've got a stored procedure which contains a fast_forward cursor. I was wondering whether it is possible to pass in an sql query string into this stored procedure for the cursor, i.e:
Create Procedure (@sqlString as text)
as
DECLARE aCursor CURSOR
FAST_FORWARD
FOR @sqlString
Hi people,
I'm using the following SP to return the rank of users but I really want it to return just a single Row column and also a Count() of the numer of Users there are....at the moment it sending me the whole table of ranked users.
Any ideas?
ALTER PROCEDURE dbo.RankUsersOnScore
(
@UserID INT
)
AS SET NOCOUNT ON;
SELECT Row, Score, UserID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Score DESC)
AS Row, Score, UserID FROM dbo.tblUserStats )
AS tblUsersRanked
I was using a proceedure i created in code but because of the way i am using it i decided that a stored proceedure will work better
i have a few add proceedures that work for insert and update but when i tried a select command, no matter what the data entered it returns the first record in the table when I fill the dataset does any one have a clue as to why it would do this
is there something you have to return through the stored proceedure
like you do when you use @@Identity
this is what i currently have,
CREATE PROCEDURE Location_Select
@p1 char(15)
AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)
go
do i need this? and if so what can i return i tried LocationID and it yelled at me saying that is an invalid column name
CREATE PROCEDURE Location_Select
@p1 char(15),
@retval int output
AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)
SET @retval =LocationID
GO
thankyou
How can you run a DST package from a stored proceedure.
I am using sql server 2000
i cant find the syntax anywhere
it is a DTS that takes a file and imports it into a table in the db
Hope someone can help.I am trying to write a stored proceedure to display sales activity by monthand then sum all the columbs.The problem is that our sales year starts in April and end in March.So far I have been able to get the sales info my using to sp's, one that saymonth >3 and the other says <4. I pass in a year parameter, that for thisyears figures would be 2003 for sp1 and 2004 for sp4.I am sure there is a better way.Below is a copy of one of my sp's.Hope you are able to help.JohnALTER PROCEDURE dbo.sp_SalesAnalFigures_P1(@Year nvarchar(50),@CCode varchar(50),@SCode varchar(50),@OType varchar(50))AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) ASMonth, SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS Sales,SUM(dbo.InvoiceItemsCostQry.TotalCost) AS Cost,SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet -dbo.InvoiceItemsCostQry.TotalCost) AS Margin,COUNT(dbo.InvoiceHeaderTbl.InvoiceNo) AS NoOfInvoices,AVG(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS AverageValueFROM dbo.InvoiceHeaderTbl INNER JOINdbo.InvoiceItemsCostQry ON dbo.InvoiceHeaderTbl.InvoiceNo =dbo.InvoiceItemsCostQry.InvoiceNoWHERE (DATEPART(yyyy, dbo.InvoiceHeaderTbl.InvoiceDate) = @Year) AND(dbo.InvoiceHeaderTbl.CompanyCode LIKE @CCode) AND(dbo.InvoiceHeaderTbl.SalesManCode LIKE @SCode) AND(dbo.InvoiceHeaderTbl.OrderType LIKE @OType)GROUP BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)HAVING (DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) > 3)ORDER BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)
View 6 Replies View RelatedHowdy team,
How would I increment a field of type 'int' with a stored proceedure with sql2005
Thanks.
CREATE PROCEDURE BatchID_Select
@Que bigint,
@retval varchar(10) OUTPUT
AS
SELECT BatchID FROM t_Que WHERE (QueID = @Que)
SET @retval =BatchID
GO
This stored proceedure will not let me save is there any reason why
if i take out the set line it says the syntax works
if i leave this line in however it says that batchid is not a valid column name
can you only return @@ variables?
i just cant figure out what its problem is
yet another question unfortunately
I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,
ie
say i have select projectid, project name from projects into temp from projects.
how can i then loop around all the rows in temp, to call my stored proceedure for each record?
in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.
can this be done for sql?
I am trying to do something like
for each record in #temp (projectid, project name) find the stored sprceedure value
so my end result will look like
projectid, project name, @storedproceedure return value
lprojectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
any help appreciated
Hope you can give me some advise.I am wanting to build a databse driven website. I am using Access toconnect to an SQL 2000 server to create tables etc.I am using ASP/ASP.Net to build my site.The question I have is on best method to retrive data. Lets say I havea Table of Products, and one of those fileds is CurrentProduct and itsTrue or False. On my web page I want to retreive and list all productsthat are marked as CurrentProduct True.I could do this as part of the SQL statement in the web page ie"Select * From Products Where CurrentProduct=True", I could create aView that only shows Current Products and use Select * FromCurrentProductsView or I could create a stored prodceedure, that looksvery much like a view.I know a bit about Access and Queries which is why I am using accessto manage Sql, but very liitle if anything about Stored Proceedures.When should I use what? And what are the advantages / dis-advantagesof each approach?Many thanks for any help you are able to provide.
View 1 Replies View RelatedI am writing a bit of code for our intranet using ASP.NET C# and SQL2005. We have a program called Aboutface that is a web based firm directory. We are using the SQL database it has in order to pull data out of it and integrate it into our intranet as we dont really care for its original interface.
I am most interested in setting up relationships.
An attorney only has one secretary that supports them, but a secretary can have many attorneys they support. This is the nature of my problem. The code below is my stored procedure. I am passing in an int which is the ID, and my goal is to generate the ID of and the name of the person who supports/is supported by. With 1 to 1 relationships, it works fine.. With more than that, it blows up because its finding multuple names (of attorneys being supported by a secretary)
I was told I might want to use a FOR EACH loop in the SP. I assume I would want to also generate a COUNT variable (to be used in the procedure and to also build the rows of my table outside of it.) and a NAME variable for each name it finds to populate the table.... but from there I am not sure if I am on the right lines of thinking or where to begin.
Any thoughts/suggestions would be greatly appreciated.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int)
AS
BEGIN
DECLARE @First as varchar(200)
DECLARE @Middle as varchar(50)
DECLARE @Last as varchar(200)
SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id ) SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
Select ISNULL(@First, '') + ' ' + ISNULL(@Middle, '') + ' ' + ISNULL(@Last,'') AS FullName
END
I have some data that I am inputing and if the record already exists i would like to add data to the fields that are not populated ONLY if they are not populated
for example
Already in the table
address= "123 this place"
city= ""
State="MI"
Zip="48462"
FirstName=""
LastName=""
being added
address= "123 this place"
city= "Ontate"
State=""
Zip="48462"
FirstName="Person"
LastName="Guy"
once i find that this record already exists because of the address and zip (which i already have complete)
I would like it to update the City first name and last name in the data that is already in the table. thank you for your help
How do you set a stored proceedure for automatic execution?--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedCan someone please help me....I have created a DNN module that works on
the test site but when I upload the module zip to a new site I get an
error on creating my stored proceedure as follows:
StartJob
Begin Sql execution
Info
Executing 01.00.00.SqlDataProvider
StartJob
Start Sql execution: 01.00.00.SqlDataProvider file
Failure
SQL Execution resulted in following Exceptions:
System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'.
Line 51: Incorrect syntax near '@Str_Title'. at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText) at
DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean
UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder
tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF
ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID],
[PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner],
[Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like
COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text
'%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN
@SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either
field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text],
[DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID =
@PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like
COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN
DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC
EndJob
End Sql execution: 01.00.00.SqlDataProvider file
The SP looks like this:
/* -------------------------------------------------------------------------------------
/ ListTAS_Journal
/ ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
@PortalID int,
@SortOrder tinyint = NULL,
@Str_Title varchar(100) = '',
@Str_Text varchar(100) = ''
AS
IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = ''
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @PortalID AND
(Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND
Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY
(CASE
WHEN @SortOrder = 1 THEN DateAdded
WHEN @SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @PortalID AND
(Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR
Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY
(CASE
WHEN @SortOrder = 1 THEN DateAdded
WHEN @SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
This SP works on the test site.
Any help would be creatly apreciated
Mark
Hi,
I have a table from which I need to create a report via MSRS2005, however the data in the table is awful in its construction and I was hoping to be able to use a stored proceedure to create a new table in which I can manupulate the data, but my T-SQL programming skills aren't that clever, so if anyone can offer any advice I'd be most grateful:
In the existing table there are two columns; StartDate and EndDate which is pretty self explanitory - what I would like to do is create a new table with only one date column and if there is more than one day between StartDate and EndDate I would like it to fill in every date in between.
For example, if the StartDate is 01/06/2007 and the EndDate 10/06/2007 I'd like the new table to list dates 01/06/2007 through 10/06/2007 inclusive in one column.
Is this possible? All suggestions welcome.
Thanks in advance,
Paul
I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..
can the stored proceedure send the text?
I have a stored proceedure that is adding a record to a database table. When the record is added using an insert statement, the ID field is autogenerated.
I have a second insert statement that inserts into a second table, however, I want/need? to use that ID field in order to link this additional information to the proper record in the initial table.
Is there an easy way to do a select or just pull the ID? I was thinking I could do a select before the final insert using 2 or 3 required fields of which used in a select altogether would be unique, but before I did that, I wanted to see if I was missing a better way. I posted the code below....
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Add_Employee_Data]
(
@LastName as varchar(100),@FirstName as varchar(100),
@MiddleName as varchar(100),@Position as varchar(100),
@Department as varchar(100),
@DirectDial as varchar(100),
@Ext as varchar(100),
@Fax as varchar(100),
@HomePhone as varchar(100),
@CellPhone as varchar(100),
@Partner as varchar(100),
@TimeKeeper as varchar(100),
@Notary as varchar(100),
@Practice as varchar(100),
@SummerAddress as varchar(250),
@SummerPhone as varchar(100),
@LegalNonLegal as varchar(100),
@Bar as varchar(100),
@OtherEmail as varchar(100),
@HomeAddressComplete as varchar(100),
@HomeAddress as varchar(100),
@HomeCity as varchar(100),
@HomeState as varchar(100),
@HomeZip as varchar(100),
@School as varchar(100),
@Degree as varchar(100),
@Status as varchar(100),
@Floor as varchar(100)) AS
DECLARE @Code as varchar(100)
INSERT Into tblMain2(LastName,FirstName,MI,Position,Dept,Extension,DirectDial,[FAX DID],HomePhone,CellularPhone,SpousePartner,
TimeKeeper,Notary,PrGroup,SummerAddress,SummerPhone,LegalNonLegal,Bar,OtherEmail,HomeAddressComplete,HomeAddress,HomeCity,
HomeState,HomeZip,Floor,Active)
Values(@LastName,@FirstName,@MiddleName,@Position,@Department,@Ext,@DirectDial,@Fax,@HomePhone,@CellPhone,@Partner,@TimeKeeper,
@Notary,@Practice,@SummerAddress,@SummerPhone,@LegalNonLegal,@Bar,@OtherEmail,@HomeAddressComplete,@HomeAddress,
@HomeCity,@HomeState,@HomeZip,@Floor,@Status)
<<<<< Put select statement here to pull in @Code where LastName = @LastName and Extension =@Ext ??
INSERT Into Education(Code,CollegeSchool,DegreeCert) Values(@Code,@School,@Degree)
my store proceedure gets the id:
CREATE PROCEDURE createpost( @userID integer, @categoryID integer, @title varchar(100), @newsdate datetime, @story varchar(250), @wordcount int) asDECLARE @newNewsID integer
Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)
SELECT @newNewsID = @@IDENTITY
then im calling it in the asp:
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046"); con.Open(); cmdselect = new SqlCommand("createpost", con); cmdselect.CommandType = CommandType.StoredProcedure;
cmdselect.Parameters.Add("@userID", userID); cmdselect.Parameters.Add("@categoryID", categoryID ); cmdselect.Parameters.Add("@title", title.Text ); cmdselect.Parameters.Add("@newsdate", newsdate.Text ); cmdselect.Parameters.Add("@story", story.Text ); cmdselect.Parameters.Add("@wordcount", "1" ); int valueinserted = cmdselect.ExecuteNonQuery(); Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted); con.Close();
as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?
i have a store procedure which i need to get the returned id from how do i do this??
the sp:
CREATE PROCEDURE createpost(
@userID integer,
@categoryID integer,
@title varchar(100),
@newsdate datetime,
@story varchar(250),
@wordcount int
)
as
Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)
im calling it from asp, maybe i need to do something here as well im a bit lost really.. any help be great!
the asp:
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();
cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;
cmdselect.Parameters.Add("@userID", userID);
cmdselect.Parameters.Add("@categoryID", categoryID );
cmdselect.Parameters.Add("@title", title.Text );
cmdselect.Parameters.Add("@newsdate", newsdate.Text );
cmdselect.Parameters.Add("@story", story.Text );
cmdselect.Parameters.Add("@wordcount", "1" );
int userinserted = cmdselect.ExecuteNonQuery();
Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id=1");
Not sure this is the right forum as I'm not sure quite what the problem is, but I have a feeeling it's the stored procedure that I'm using to replace the SQL string I used previously.I have a search form used to find records in a (SQL Server 2005) db. The form has a number of textboxes and corresponding checkboxes. The user types in a value they want to search for (e.g. search for a surname) and then selects the corresponding checkbox to indicate this. Or they can search for both surname and firstname by typing in the values in the correct textboxes and selecting the checkboxes corressponding to surname and firstname.The code to make this work looks like this:---------------------------------------- Dim conn As SqlConnection Dim comm As SqlCommand Dim param As SqlParameter Dim param2 As SqlParameter Dim param3 As SqlParameter Dim param4 As SqlParameter Dim objDataset As DataSet Dim objAdapter As SqlDataAdapter conn=NewSqlConnection("blah, blah") comm = New SqlCommand 'set properties of comm so it uses conn & recognises which stored proc to execute comm.Connection = conn comm.CommandText = "SPSearchTest3" comm.CommandType = CommandType.StoredProcedure 'create input parameter, set it's type and value param = comm.CreateParameter param.ParameterName = "@empid" param.Direction = ParameterDirection.Input param.Value = txtPatID.Text param2 = comm.CreateParameter param2.ParameterName = "@LastName" param2.Direction = ParameterDirection.Input param2.Value = txtSurname.Text comm.Parameters.Add(param) comm.Parameters.Add(param2) conn.Open() objAdapter = New SqlDataAdapter(comm) objDataset = New DataSet objAdapter.Fill(objDataset) dgrdRegistration.DataSource = objDataset dgrdRegistration.DataBind() conn.Close()------------------------------------While the stored procedure is this:------------------------------ @EmpID int, @LastName nvarchar(20) ASSELECT EmployeeID, LastName, Firstname, BirthDate, Address, title, addressFROM employeesWHERE (DataLength(@EmpID) = 0 OR EmployeeID = @EmpID)AND (DataLength(@LastName) = 0 OR LastName = @LastName)------------------------------This will work if I search using EmployeeID and Surname or only by EmployeeID, but I don't get any results if I search only for Surname, even though I know the record(s) exits in the db and I've spelled it correctly. Can someone point out where I'm going wrong?(Incidentally if I have a procedure with has only one parameter 'surname' or 'employeeID', it works fine!)Thanks very much and sorry about the long-winded post.
View 6 Replies View RelatedHiI have a problem trying to compare a string value in a WHERE statement. Below is the sql statement. ALTER PROCEDURE dbo.StoredProcedure1(@oby char,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT
)
ASSELECT * FROM
(
SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) The problem is the Area variable if i enter it manually it works if i try and pass the variable in it doesn't work. If i change ([Area] = @Area) to ([Area] = 'The First Area') it is fine. The only problem i see is that the @Area has spaces, but i even tried passing 'The First Area' with the quotes and it still didnt work.Please help, its got to be something simple.Thanks In Advance
Up till now I've used SP's for updates and only ever needed to return error messages.
Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)
It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.
Hope that makes sense.
Thanks
Mark
Hi Guys,
I have an nVarChar field named "Event" (I know - I didn't name it !) with variable length values such as *ALARM* or *RESTORE*
They always start or end with a * and I want to trim them off before returning the data to my app.
I've got rid of the first one with...
LTRIM(STUFF(Event, 1, 1, ''))
Any tips on how to get rid of both of them in one go ?
Thanks in advance.
Steve.
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View 1 Replies View RelatedI try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code dr = cmd.ExecuteReader();
_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();
Label1.Text = _xmlFile; and this is what I get "12"Maybe I missed something to get the whole XML String
This procedure gives a error : " Msg 245, Level 16, State 1, Procedure YAMAN, Line 16
Conversion failed when converting the nvarchar value 'user' to data type int. "
How can i return string value
ALTER procedure [dbo].[YAMAN]
(@username varchar(20),@active varchar(20))
as
begin
if exists (select username from aspnet_Users where username=@username)
begin
if @active=(select active from aspnet_Users where username=@username)
return 'already exist'
else
begin
update aspnet_Users set active=@active where username=@username
return 'update'
end
end
else
return 'user does not exist'
end
Yaman
I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.
I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.
Here is what I know needs to be done but not sure how to put into the procedure.
CREATE proc ChangeHIS
as
--Declare Variables
Declare @LastName varchar,
@FirstName varchar,
@MI varchar,
@ID varchar,
@Dept varchar,
@intCount int,
@UserName varchar,
@strTemp varchar
--Create Temporary Table
CREATE TABLE [EmployeeAudit].[dbo].[tmpTable] (
[UPUPRF] varchar (10),
[UPTEXT] varchar (50)
)
select [UPUPRF], [UPTEXT] from tblHIS into tmpTable
GO
And something dealing with the below code as well.
@tmpString = RTRIM(LTRIM(@tmpString))
If charindex(@tmpString, ",") > 0
--'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC'
@LastName = Left(@tmpString, charindex(@tmpString, ","))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, ",") + 1)))
--'Michael J - 78672 - SR MIS SUPPORT SPEC'
@FirstName = Left(@tmpString, charindex(@tmpString, " "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " ") + 1)))
If charindex(@tmpString, "-") > 1
--'J - 78672 - SR MIS SUPPORT SPEC'
@MI = Left(@tmpString, 1)
@tmpSting = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - 2)
End
--'- 78672 - SR MIS SUPPORT SPEC'
@ID = Left(@tmpString, charindex(@tmpString, " - "))
@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " - ") + 3)))
--'SR MIS SUPPORT SPEC'
@Dept = @tmpString
End
Hope someone can point me in the right direction
In 'MyTable' I have the following columns...
TotalNumber (numeric)
Weighting (numreric)
Hours (numeric)
Minutes (numeric)
Formula (nvarchar)
'Formula' column stores a literal string of the formula that may include some, none or all of the previous columns or be NULL.
Here are some examples of the actual strings it stores...
Weighting*Hours
Weighting+(TotalNumber*Hours)
Weighting*Hours)+(TotalNumber*2)
etc etc
All I want to do is create a UDF that will evaluate these strings as math formula and return the value depending on the values of the other columns in the row.
Bear in mind that there may not be a string formula at all for some rows, in which case the value of teh Hours column alone should be the result.
I can do this in vb using the 'Replace' function but am having difficulty in translating it over to T-SQL.
Here is the vb version i use in Ms Access...
getFormula(strDutyType As String, dblTotalNumber As Double, dblWeightingAs Double, dblHours As Double, dblMinutes As String, strFormula As Variant)
If IsNull(strFormula) Or strFormula = "" Then
getFormula = dblHours
Exit Function
End If
'Create the expression string with literal values
strExpression = Replace(strFormula, "TotalNumber", _
CStr(dblTotalNumber), , , vbTextCompare) _
strExpression = Replace(strExpression, "Weighting", _
CStr(dblPF), , ,vbTextCompare)
strExpression = Replace(strExpression, "Hours", _
CStr(dblHours), , , vbTextCompare)
strExpression = Replace(strExpression, "Minutes", _
CStr(dblMinutes), , , vbTextCompare)
'Evaluate the math of the literal expression
getFormula = Eval(strExpression)
-----------
Many Thanks
Ever needed to find a stored procedure with a specific string in it? You can pretty this up as a stored procedure and pass it a parm or cut and paste it into query analyzer.
select name from sysobjects
where id = (select id from syscomments
where text like '%like%')
Edit:
The above works only for a single hit. For multiple hits, this works
select name from sysobjects as A
join syscomments as B
on (text like '%cursor%')
where A.id = B.id
Live and learn,
Cat
Edited by - cat_jesus on 08/22/2001 10:09:49
Edited by - cat_jesus on 08/22/2001 10:10:29
I have SQL table (tblUsers) and one of the fields holds the email address. I want to step through each record build a multiple email string to send to a lot of people. It would look like this
Str_email = Me@hotmail.com;Andy@Hotmail.com;Fred@Hotmail.com
I then want to pass Str_email back to an asp.web page
Can this be done in a stored procedure ?
Is there anyway to extract part of a string in a stored procedureusing a parameter as the starting point?For example, my string might read: x234y01zx567y07zx541y04zMy Parameter is an nvarchar and the value is: "x567y"What I want to extract is the two charachters after the parameter, inthis case "07".Can anyone shed some light on this problem?Thanks,lq
View 4 Replies View RelatedI'm passing a comma delimited string to my SP, e.g.:"3,8,10,16,23,24"I need to retreive each number in this string and for every number found I need to execute some sode, say add "AND SportID="+numberfoundHow can I do that?
View 6 Replies View RelatedHello,
I was wondering if it's possible to pass in a comma separated string
"12,14,16,18" and use it in a stored procedure with "IN" like this:
@SubRegions varchar(255) <-- my comma separated string
SELECT *
FROM myTable
WHERE tbl_myTable.SubRegionID IN (@SubRegions)
It tells me it has trouble converting "'12,14,16,18'" to an INT. :(
Hello, I'm trying to pass a simple string value to a stored procedure and just can't seem to make it work. I'm baffled since I know how to pass an integer to a stored procedure. In the example below, I don't get any compile errors or page load errors but my repeater doesn't populate (even though I know for certain the word "hello" is actually in the BlogTxt field in the db. If I change the stored procedure to say...WHERE BlogTxt LIKE '%hello%'then the results do indeed show up in the repeater.I ultimately would like to pass text from a textbox control or maybe even a querystring to the stored procedure. Then I'll move on to passing multiple "keywords" to it. :)My relevant code is below. Thanks in advance for any help.*******************ViewData.ascx.vb file*******************Private strSearch As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadTry Dim objBlogController As New BlogController 'for testing purposes strSearch = "hello" repeaterSearchResults.DataSource = objBlogController.SearchBlog(strSearch) repeaterSearchResults.DataBind()Catch exc As Exception ProcessModuleLoadException(Me, exc)End TryEnd Sub----------------------------------------
*******************Controller.vb file*******************
Public Function SearchBlog(ByVal strSearch As String) As ArrayList Return CBO.FillCollection(DataProvider.Instance().SearchBlog(strSearch), GetType(BlogInfo)) End Function----------------------------------------
******************* DataProvider.vb file*******************
Public MustOverride Function SearchBlog(ByVal strSearch As String) As IDataReader----------------------------------------
*******************SqlDataProvider.vb file*******************
Public Overrides Function SearchBlog(ByVal strSearch As String) As IDataReader Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "SearchBlog", strSearch), IDataReader)End Function----------------------------------------
*******************Stored Procedure*******************
CREATE PROCEDURE dbo.SearchBlog @strSearch varchar(8000)AS
SELECT ItemID, PortalID, ModuleID, UserID, BlogTxt, DateAdd, DateModFROM BlogWHERE BlogTxt LIKE '%@strSearch%'GO----------------------------------------