Variables In Query Strings (that Are Stored In A Db)

Oct 28, 2004

Hi all,

If I have a query string that is to be stored in a database, for example


Code:

SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'



how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.

Is there an appropriate way of doing this in MS SQL Server?

Thanks

Tryst

View 1 Replies


ADVERTISEMENT

Building Query Strings Within Stored Procs - Good Or Evil?

Feb 4, 2008

I ran across this technique being used in an application the other day. It seems not a good idea to me. What do you think?

1. The proc builds a basic query, nothing real fancy, into a string variable called @SQL defined as varchar 2000. Depending on the result desired, the group by clause can be one of three different sort orders.

2. The string is executed via EXEC @SQL.

It seems to me that the whole process can eliminate the EXEC and just use some other construct. All the parameters are passed in via the initial call to the stored proc. It also seems that every time this is executed it will result in a new query compile and cache useage, no matter what. Wasteful? Should I take the developers aside and knock heads? I think the app was coded by some folks who were rookies then but may be willing to crack open their code. Or, am I the one that is a rookie?

Thanks for your inputs.

View 11 Replies View Related

Building SQL Strings From Variables

Dec 9, 2006

Disclaimer... I am very new to SQL server!

I am trying to create a stored proc. I am passing in some variables which are used as the "data" side of where clause tests, but I also want to pass in a couple of variables to be the variable side of the where clause test, can it be done?

i.e. select id from table where a=1 and b=2 order by my_order_field

becomes

select id from table where a=@data1 and b=@data2 order by @my_variable_field

and I call the sp with exex sp 1,2,my_order_field

Any clues?

View 10 Replies View Related

Putting Variables In SQL Strings

Apr 11, 2008

Atm this is my Test SQL String

"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT 'Test' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTESTarchived est.pdf', SINGLE_BLOB) AS BLOB"

When I try to put in a variable as follws

"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT '" + @[User::MyFileValue] + "' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTestarchived est.pdf', SINGLE_BLOB) AS BLOB"

I keep getting errors Saying it contains an illegal escape sequence of w any ideas ?

Btw the above is used as an expression

View 4 Replies View Related

Stored Proc: Query Vals To Local Variables

Aug 18, 2004

I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.

Thats the dream.

The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.

Here is what I have as of this moment for my sp.


ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/

PRINT @dtPP

RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/


The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '

Any help would be greatly appreciated as I am about to kick someone/something.

Thanks

View 2 Replies View Related

Creating Sql Strings In Stored Procs

Jul 31, 2001

Hi Guys,

I have written a piece of code in a stored procedure that builds a string called "filter$" based on fielde in a table.
How do I use this string as my where clause?

in vb I would use:
rs.open filter$,cn

I hope this makes sense and someone can help me

View 2 Replies View Related

Search Strings, Stored In A Table...?

Dec 21, 2007

Hey, i have multiple search strings and I don't want to keep track of the search strings in SQL format.

As in, i don't want to do this anymore:

Select *
From tbl_Name
where FirstName = 'John' or

FirstName = 'Mike' or
FirstName Like 'Jen%' or
...
and the list continues for at least a 100...


what i want to do is store all the search values in a table and then join on that table as a search...

Create Table as tbl_SearchValues
[FirstName] as Varchar(50)

Insert Table tbl_SearchValues values ('John', 'Mike', 'Jen%')


Select *
From tbl_name n Join tbl_SearchValues s
on n.FirstName Like s.FirstName

This doesn't work cause I tried it... but does anyone know a different workaround??

View 2 Replies View Related

Help With Query Strings

Feb 7, 2007

I am using query strings to pass data from web form to web form and I have two questions.  First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it.  So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work.  How can I fix this using the asp:sql datasource my aspx code looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"
SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)">
<SelectParameters>
<asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string
Dim y as string  = "'" // " ' "
key = Request.QueryString("key").trim(y.tochararray)
But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key.  How does this need to be changed up?
string y = "'";
key = Request.QueryString["key"].trim(y.tochararray());

View 3 Replies View Related

Comparing Two Strings Inside A Stored Procedure

Aug 14, 2006

Basically I have two strings.  Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place.  Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data.  lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>"
Is there an way to accomplish this inside a stored procedure?

View 2 Replies View Related

Passing An Array Of Strings To A Stored Procedure

Jun 10, 2007

Well, I managed to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@IDList varchar(500),
@ReorderLevel int,
@ProductName nvarchar(30)
 
)
AS
BEGIN
SET NOCOUNT ON
 EXEC('Update dbo.Products
SET ReorderLevel = (' + @ReorderLevel + ') ,ProductName = (''' + @ProductName + ''')
WHERE ProductID IN (' + @IDList + ')')
END
 ----------------------
THis works fine inside Sql Server 2005 Query analyser.
 But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.
 
Any help would be appreciated
And can someone convert it to support XML instead of list of strings. thanks.
 

View 3 Replies View Related

Parsing Character Strings In A Stored Procedure

Jul 11, 2001

Hi guys,

I'm trying to parse a character string in a stored procedure but I'm not sure how to do it. For example:

-------------------------------
create procedure sp_test (
@fld varchar(1000)
)
AS

???
???
.
.
.
.
----------------------------------

sp_test '123:abc,456:def,789:ghi'


I need to put 123 into a seperate local variable, abc into a local variable, and so on...

What would be the best function to use?

Any help would greatly be appreciated.

- Gary

View 1 Replies View Related

Returning Mutliple Strings From Stored Proc

May 14, 2007

Hey guys, could somebody pls provide me with an easy example as to how you would return multiple strings from a stored proc? Even a link to a decent tut would be great!

Muchos gracias!

View 12 Replies View Related

Problem With DateTime And Strings In Stored Procedures

Jul 23, 2005

I'm keeping in the database a log of all the sessions for myapplication. I'm trying to write a stored procedure that returns allthe sessions that; the login contains a certain string, loggedin aftera certain datetime and loggedout before another datetime. Anycombination of these parameters can be used and, if none, returns allthe log.Below is the code I came up with but I'm having a "Syntax errorconverting datetime from character string" exception. When not usingDateTime parameters everything works fine. Can you tell me how can Iavoid this exception? Thanks in advance...ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining(@Pattern Varchar(255),@From DateTime,@To DateTime)ASDECLARE @Query VARCHAR(500)SET @Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERESessionHistoryItems.SessionId = Sessions.SessionId'DECLARE @conditions nvarchar(257)SET @conditions = '';IF LEN(@Pattern) > 0 BEGINSET @conditions = @conditions + ' Sessions.Login LIKE ''%' + @Pattern+ '%'''ENDIF @From IS NOT NULL BEGINIF LEN(@conditions) > 0 BEGINSET @conditions = @conditions + ' AND 'ENDSET @conditions = @conditions + ' SessionHistoryItems.LoggedOutAt >=' + @FromENDIF @To IS NOT NULL BEGINIF LEN(@conditions) > 0 BEGINSET @conditions = @conditions + ' AND 'ENDSET @conditions = @conditions + ' SessionHistoryItems.LoggedInAt <= '+ @ToENDIF LEN(@conditions) > 0 BEGINEXEC(@Query + ' AND ' + @conditions)ENDELSE BEGINEXEC(@Query)ENDRETURN

View 1 Replies View Related

SQLDataSource With Query Strings

Mar 16, 2007

Hi, I have a SQLDataSource binding to a GridView and can come to the page either with or without a query string attached:
 /ProjectManagement/reporting/project.aspx
/ProjectManagement/reporting/project.aspx?portfolio=3
When it comes with a query string, I can see in SQL Server profiler it executes and I get all the right data. When it is an empty string, or with no "?portfolio=" on it, it won't even execute against SQL server. Any ideas?
<asp:GridView ID="grid" runat="server" Width="600px"
ShowHeader="false"
AutoGenerateColumns="false"
DataSourceID="DSportfolio"
AllowSorting = "true"
AllowPaging = "true">
<Columns>
<asp:TemplateField>
<ItemTemplate>
 .............
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="DSportfolio" ConnectionString="<%$ AppSettings:SQLConnection1 %>"
SelectCommand="uspSELECT_PROJECT"
SelectCommandType="StoredProcedure"
runat="server">
<SelectParameters>
<asp:QueryStringParameter Name="p_PORTFOLIOID" QueryStringField="portfolio" />
</SelectParameters>
</asp:SqlDataSource>
Thanks,
James

View 2 Replies View Related

Getting SQL To Lookup Query Strings

Jun 10, 2008

Hi, I am having a problem looking up querystrings in my DB, I have the following code
   <asp:SqlDataSource ID="SqlData_products" runat="server" ConnectionString="<%$ ConnectionStrings:ProductDatabaseConnectionString2 %>"         SelectCommand="SELECT * FROM [tbl_subProduct],[tbl_subCategory],[tbl_topCategory],[tbl_Material],[tbl_topLevelProduct] WHERE numSubCategoryID = @Category OR numMaterialID = @Material OR txtOrderCode = @Keyword OR txtMovexCode = @Keyword OR txtUKMapCode = @Keyword">           <selectparameters>           <asp:QueryStringParameter Name="Category" QueryStringField="SearchCatString" />           <asp:QueryStringParameter Name="Material" QueryStringField="SearchMatString" />           <asp:QueryStringParameter Name="Keyword" QueryStringField="SearchKeyString" />           </selectparameters>    </asp:SqlDataSource>
 My Querystrings are in the VB file:- SearchCatString = Request.QueryString("txtSelCat")
SearchMatString = Request.QueryString("txtSelMat")
SearchKeyString = Request.QueryString("txtKeyword")These come from a previous page, where a dropdown list copies the ID of the selected item into a text box. My problem is that this is not working please help

View 1 Replies View Related

Trouble Formatting Strings In Email Stored Procedure

Aug 9, 2007

Hello,I have a sqlserver stored procedure that calls the stored procedure sp_send_cdosysmail_htm to send reminder emails to customers. I am experiencing problems when trying to concatenate the id being renewed in the subject field. I'm always getting the message "error 170: line 10: Incorrect syntax near '+'."Does anyone know what the error means or can point me to a resource on the web? Many thanksRitao  CREATE PROCEDURE dbo.SendRenewalEmail         @ID INT AS         BEGIN                 exec dbo.sp_send_cdosysmail_htm                         @From = 'yosemite.sam@acme.com',                         @To = 'road.runner@acme.com',                         @Cc = null,                         @BCC = null,                         @Subject =  'Order  '  + @ID + 'Renewal Reminder',                         @Body = 'Hello roadrunner....'         ENDGO 

View 4 Replies View Related

Stored Procedure To Count Matching Words Between Two Strings

Jan 19, 2007

hi all,

I want to know that how can i write a stored procedure that takes an input param, i.e., a string and returns the count of the matching words between the input parameter and the value of a column in a table. e.g. I have a table Person with a column address. now my stored procedure matches and counts the number of words that are common between the address of the person and the input param string. I am looking forward for any help in this matter. I am using Sql server 2005.

View 1 Replies View Related

Concatenate Strings After Assigning Text In Place Of Bit Strings

Feb 19, 2007

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:


select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

View 2 Replies View Related

Building Dynamic SQL Query Strings

Jan 17, 2008

Let me start by asking that no one try to convince me to use Stored Procs.  The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need.
I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc.  From this, I have a "core" query that selects the fields that display in my GridView.  It has a base Select clause, From clause and Where clause.  From this I then add more to the Where clause to apply these filter values.
Here's an example "core" query:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode
From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC')
My code would add the last line above since the user specified that they only wanted NC profiles.
This is very simple and I have this already going on with my application.  Here's the problem.  In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables.  Many times I include tables that have no data to display or filter on and therefore impacts performance.  Here's an example:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID
From the query above, I have included the Agent table that holds the agent's contact information.  One of my filters allows the user to type in an agents name to find all profiles assigned to it.  Here's what that would look like:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John'
You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me.
The obvious thing would be to only include the Agent table when searching for an agent name.  This is ultimately what I'm looking to do, but I need a solid method to go about doing this.  Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it.
If anyone has any ideas on how to simplify this process I'm selcome to suggestions.  We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference.  I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible.
This app is using .NET 2.0 and written in VB.NET.  Thanks for any help!

View 14 Replies View Related

Query On Empty Strings In A Table

Oct 25, 2007

Hey

Is it possible to search for a column without a value?

$query="select id from table1 where col2=''"; (this didnt work, but how do I do it??)

I need the id for the row that has the col2 empty.

:)

View 13 Replies View Related

LIKE Query With Table Of Wildcard Strings

Mar 7, 2008

The sql below is a simple example of LIKE query with a table of wildcard strings. I hope this helps others.

declare @LikeTable Table
(
LikeValue nvarchar(50)
)

insert into @LikeTable (LikeValue) values ('%blah')
insert into @LikeTable (LikeValue) values ('%abc%')
insert into @LikeTable (LikeValue) values ('%edf%')
insert into @LikeTable (LikeValue) values ('car%')
insert into @LikeTable (LikeValue) values ('%ome%')

declare @CompareValue nvarchar(50)
set @CompareValue = 'some value'

select * from @LikeTable where (@CompareValue like LikeValue)

View 4 Replies View Related

Storing All Stored Procedures In One Database To Minimize Different Connection Strings

Jul 20, 2005

In order to minimize the number of connection strings I have to use toaccess different databases on the same Sql Server, I was consideringstoring all stored procedures in just one database. I want to do thisbecause connection pooling in my application - ASP.NET is based onthis connection string. So if I need to access 6 different databaseson one sql server & set 6 different connection strings, I end upcreating 6 different connection pools.Other than it might create more management work for the DBA, are thereany performance implications with implementing this scheme? Do storedprocedures run any slower if they access tables that are stored indifferent databases within the same server?Any comments/suggestions are appreciated.TIA,Minh Tran

View 4 Replies View Related

Custom SiteMap - Search By Several Strings In One Query

Mar 4, 2008

I am building myself a datadriven menu control and I have got one table where I store all menu items as rows. On each row there is a column for roles, in this column I have added one or several roles as a string. Today I can retrieve all menu items (rows) for a requested role, but how can I retrieve menu items for two or more roles? I have each role inserted as rows in a temp table (@Role), if that makes it easier?DECLARE @Role TABLE (     role varchar(15)) SELECT SiteMap.Id, SiteMap.Title, SiteMap.UrlFROM SiteMapWHERE (CHARINDEX(@Roles, SiteMap.Roles) > 0 ) Regards, Sigurd

View 2 Replies View Related

Sample Query For Concatenating Two Strings And Using It As Column

Jan 5, 2005

hi,
please check this query and reply back with the appropriate solution.
len(ltrim(rtrim(exec('select' ' ' + 'pay' +convert(substring(@y1,3,2), varchar 2)))))<>0
here the concept is concatenating two string then that result is used as column and retreiveing data.but this is considering it as string instead of column.
can anyone give an appropriate solution.

Regards,
Uma.

View 2 Replies View Related

T-SQL (SS2K8) :: When Script Out A Stored Procedure It Encloses Strings In Double Quotes

Jul 10, 2014

All of a sudden hen I script out a Stored Procedure it encloses strings (edit) in Double Quotes?

For example ANDPromo.[Group] IN (''FL_Small'',''FL_Large'')

Also it generates this code that I do not want. I just was Create Procedure...

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_IncentiveReport]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

What options do I need to set?

View 5 Replies View Related

Difficult Query: Return Recordset From Concatenated Strings?

Jul 20, 2005

Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray

View 7 Replies View Related

Values In LEFT JOIN Need To Be Empty Strings (was SQL Query Question)

Sep 26, 2006

Hello,

I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.

I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.

TableC and TableD are guaranteed to have a relationship.

The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.

Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.

Thank you SO much for your help!

View 5 Replies View Related

Running Query After Query And Input Variables

Jul 8, 2013

I have 2 requests for desperate Hélio..

1) is there any way to run a query over a query without having to create a table with the results of the first query? (would drop table work? If so, how?

2) how can i define input variables the same way i do in excel? I am trying to run a couple of simulations based on 2 core inputs (in excel i would just do a data table)

View 7 Replies View Related

Stored Procedure Variables

Sep 19, 2000

Hi!
I am kind of new to the sequel server and I have to write a stored procedure to create raw of my table. I wrote the following stored procedures, but I am not sure weather I can use variables in select statements as in the following procedure. Could you please explain what is the problem, why I am getting an error, when I am trying to create it? <The error says that there is an incorrect syntax near '@tbleName' in lines 21, 25 and 30>

Thank you very much!
Elly.

/* Inserts raw into the talble tbleName. Called from other procs.
tbleName: The name of the table to insert
other variables: values to insert
checkParentId,..: items to be checked upon-parentid, position, parent primary key
Returns:
0 : Successfully inserted a raw into the table
-1 : Position to insert is taken
-16: The specified parent does not exist
-17: Insert was unsuccessful
*/
create procedure sp_acml_createRaw (@tbleName VARCHAR(50), @nameN VARCHAR(50),
@hrefN VARCHAR(50), @posN VARCHAR(50), @parentIdN VARCHAR(50),
@nameV VARCHAR(150),@hrefV VARCHAR(255),
@posV SMALLINT, @parentIdV SMALLINT,
@checkParentId VARCHAR(50), @checkPosition VARCHAR(50),
@checkParentKey VARCHAR(50))
as
BEGIN
/* Check weather the parent exist */
IF exists (select @parentIdN
from @tbleName
where @tbleName.@checkParentKey = @parentIdV) /* if parent exist */
BEGIN
/* Check weather the isertion position is correct */
IF exists (select @checkParentId from @tbleName
where @tbleName.@checkParentId = @parentIdV AND
@tbleName.@checkPosition = @posV)
return -15 /*Insertion position is incorrect - it is taken already*/
/* Insertion postion is correct */
insert into @tbleName(@tbleName.@nameN, @tbleName.@hrefN, @tbleName.@posN, @tbleName.@parentIdN)
values (@nameV, @hrefV, @posV, @parentIdV)
IF (@@ERROR != 0)
return -17 /* Insert was unsuccessful */
return 0
END
return -16 /* The parent specified was not found */
END
/* End of sp_acml_createRaw */

View 2 Replies View Related

Using Variables In Stored Procedure

Aug 25, 2004

I am writing a stored procedure that basically gets a whole lot of info. Simple enough... that part works. Now I want to add functionality to pass in sortBy and direction variables so the results can be sorted from a web page. The code I have is:


Code:


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER procedure GetRecruiterApplicants
@useridvarchar(50),
@trackerGroup varchar(50),
@sortBy varchar(20),
@dir varchar(5)

as
begin

set nocount on

SELECT C.USERID INTO #VU FROM VUSERS V WITH (NOLOCK), COMPANIES C WITH (NOLOCK)
WHERE CHARINDEX(','+C.USERID+',', ','+VUSERS+',')>0 AND V.USERID=@userid
UNION SELECT @userid

SELECT distinct l.AccessCode, l.ApplicantGivenName, l.ApplicantFamilyName, l.DateCreated, l.DateApplicantAccessed, p.ApplicationTitle, l.disabled
FROM chamslinks l, chamsProjectIDs p, chamsGroups g
WHERE l.TrackerID IN (SELECT userid FROM #VU)
AND g.trackerGroup = @trackerGroup
AND p.groupTblID = g.groupTblID
AND p.ProjectID = l.ProjectID
ORDER BY l.@sortBy @dir

DROP Table #VU

set nocount off
end



The error I am getting is: "Server: Msg 170, Level 15, State 1, Procedure GetRecruiterApplicants, Line 24
Line 24: Incorrect syntax near '@dir'."

Any help?

View 5 Replies View Related

Stored Procedures Variables

Dec 28, 2005

I have a PROC that I want to modify via a variable I will pass in from ASP.Net.

Currently the PROC is as follows:

ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(30)
AS
Set @Search = '%'+ @Search + '%'

SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
WHERE (dbo.EDCclient.CLIENT like @Search) OR
(dbo.EDCproperty.PROPERTY like @Search)

I now want to modify it so that the entire Where clause is created in ASp and passed in. So I think my new PROC should look as follows:

ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(200)
AS

SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID

@Search)

But I can't get SQL to accept the @Search in place of the Where... Any ideas?

View 7 Replies View Related

Stored Procedure Variables

Aug 20, 2007

i ahve the following procedure

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




create PROCEDURE testflu @p_acad_period nvarchar (5)
AS

SET NOCOUNT ON


/* Base Table fields */
DECLARE @t_student_id nvarchar (11)
DECLARE @full_time_student bit
DECLARE @student_id nvarchar (11)
DECLARE @acad_period nvarchar (5)
DECLARE @aos_code nvarchar (11)
DECLARE @aos_period nvarchar (5)
DECLARE @full_desc_static nvarchar (200)
DECLARE @full_desc_session nvarchar (200)
DECLARE @dept_code nvarchar (10)
DECLARE @aos_start_dt datetime
DECLARE @exp_length smallint
DECLARE @unit_length nvarchar (10)
DECLARE @student_year tinyint
DECLARE @hrs_per_week int
DECLARE @aos_end_dt datetime
DECLARE @no_of_weeks numeric
DECLARE @hrs_total decimal (18,2)
DECLARE @main_course_study_is_C2K tinyint
DECLARE @hrs_actual decimal (18,2)
DECLARE @hrs_actual_student decimal (18,2)
DECLARE @hrs_notional_course decimal (18,2)
DECLARE @hrs_notional_student decimal (18,2)
DECLARE @student_type nvarchar (10)
DECLARE @moa_code nvarchar (10)
DECLARE @qual_aim nvarchar (3)
DECLARE @numeric_qual_aim int
DECLARE @subject_code nvarchar (4)
DECLARE @subject_area_main nvarchar (10)
DECLARE @subject_area_group nvarchar (10)
DECLARE @course_group char (1)
DECLARE @FE_HE_type char (2)
DECLARE @nvq_lvl_ind nvarchar (1)
DECLARE @geolocn_code nvarchar (10)
DECLARE @surname nvarchar (40)
DECLARE @forename nvarchar (40)
DECLARE @birth_dt datetime
DECLARE @post_code nvarchar (8)
DECLARE @student_attend_mode nvarchar (10)
DECLARE @funding decimal (18,2)
DECLARE @key_skill_count smallint
DECLARE @additionality_yn bit
DECLARE @fee_waiver nvarchar (10)
DECLARE @start_date datetime
DECLARE @student_status nvarchar (10)
DECLARE @end_date datetime
DECLARE @spec_learn_disab nvarchar (2)
DECLARE @sldd_ind tinyint
DECLARE @age smallint
DECLARE @full_time_ind tinyint
DECLARE @age_1960_ind tinyint
DECLARE @tsn_ind tinyint
DECLARE @link_ind tinyint
DECLARE @ESOL nvarchar (3)
DECLARE @esol_ind tinyint
DECLARE @infill_ind tinyint
DECLARE @RP_entitlement tinyint
DECLARE @threshold1 datetime
DECLARE @threshold2 datetime
DECLARE @threshold3 datetime
DECLARE @student_full_cost_ind tinyint
DECLARE @fundable_ind tinyint
DECLARE @LP_entitlement tinyint
DECLARE @LP2_entitlement tinyint
DECLARE @LP3_entitlement tinyint
DECLARE @outcome nchar (10)
DECLARE @OP_entitlement tinyint
DECLARE @tenhour_ind tinyint
DECLARE @basicIT nchar (3)
DECLARE @basic_it_ind tinyint
DECLARE @special_needs_ind tinyint
DECLARE @Evening_course tinyint
DECLARE @RP_basic decimal (18,2)
DECLARE @RP_NTETS_weight decimal (18,2)
DECLARE @RP_enhance_NTETS decimal (18,2)
DECLARE @RP_enhance_Age decimal (18,2)
DECLARE @RP_enhance_TSN decimal (18,2)
DECLARE @RP_enhance_IT decimal (18,2)
DECLARE @RP_enhance_SLDD decimal (18,2)
DECLARE @RP_enhance_ESOL decimal (18,2)
DECLARE @RP_total decimal (18,2)
DECLARE @LP_basic decimal (18,2)
DECLARE @subj_area_weight decimal (18,2)
DECLARE @LP_weighted decimal (18,2)
DECLARE @LP_enhance_SLDD decimal (18,2)
DECLARE @LP_enhance_VOC decimal (18,2)
DECLARE @LP_enhance_AGE decimal (18,2)
DECLARE @LP_enhance_TSN decimal (18,2)
DECLARE @LP_enhance_HE decimal (18,2)
DECLARE @LP_enhance_FEE decimal (18,2)
DECLARE @LP_total decimal (18,2)
DECLARE @OP decimal (18,2)
DECLARE @spurs_total decimal (18,2)
DECLARE @student_SPURS_total decimal(18,2)
DECLARE @out_weight decimal (18,2)
DECLARE @main_course_study tinyint
DECLARE @exp_days int
DECLARE @act_days int
DECLARE @total_students bigint
DECLARE @total_enrolments bigint
DECLARE @acad_year_spurs decimal (18,2)
DECLARE @RP_1960 tinyint
DECLARE @RP_null_DOB tinyint
DECLARE @RP_TSN tinyint
DECLARE @RP_disab2 tinyint
DECLARE @RP_disab3 tinyint
DECLARE @RP_disab4 tinyint
DECLARE @RP_ESOL tinyint
DECLARE @LP_1960 tinyint
DECLARE @LP_null_DOB tinyint
DECLARE @LP_TSN tinyint
DECLARE @LP_disab2 tinyint
DECLARE @LP_disab3 tinyint
DECLARE @LP_disab4 tinyint
DECLARE @LP_infill tinyint
DECLARE @LP_infill_1960 tinyint
DECLARE @LP_infill_null_DOB tinyint
DECLARE @LP_infill_TSN tinyint
DECLARE @LP_infill_disab2 tinyint
DECLARE @LP_infill_disab3 tinyint
DECLARE @LP_infill_disab4 tinyint
DECLARE @LP_term2 tinyint
DECLARE @LP_term2_1960 tinyint
DECLARE @LP_term2_TSN tinyint
DECLARE @LP_term2_disab2 tinyint
DECLARE @LP_term2_disab3 tinyint
DECLARE @LP_term2_disab4 tinyint
DECLARE @LP_term3 tinyint
DECLARE @LP_term3_1960 tinyint
DECLARE @LP_term3_TSN tinyint
DECLARE @LP_term3_disab2 tinyint
DECLARE @LP_term3_disab3 tinyint
DECLARE @ndaq_qual nvarchar (12)



DECLARE @base CURSOR


UPDATE delflu
SET tot_students = 0,
tot_enrolments = 0,
flu_total = 0,
processing = 1,
process_start = GETDATE(),
process_end = NULL
WHERE acad_period = @p_acad_period


DELETE FROM delflubase
WHERE (acad_period = @p_acad_period)

SET @total_students = 0
SET @total_enrolments = 0
SET @acad_year_spurs = 0.00

SET @base = CURSOR LOCAL FAST_FORWARD
FOR

SELECT stmaos.student_id, stmaos.acad_period, stmaos.aos_code, stmaos.aos_period, stcstatd.full_desc, stcsessd.full_desc AS SessionDescription,
stcsessd.dept_code, stcsessd.aos_start_dt, stcsessd.exp_length, stcsessd.unit_length, ISNULL(stmaos.student_year, 0) AS student_year,
stcsessd.hrs_per_week / 100 AS hrs_per_week, stcsessd.aos_end_dt, stcsessd.no_of_weeks, stcsessd.hrs_total / 100 AS hrs_total,
stmfesqa.student_type, stcsessd.moa_code, stcfesdt.qual_aim, stcfesdt.subject_code, delsubj.subj_area, ISNULL(delsubj.subj_course_group, 0)
AS subj_course_group, stcfesdt.nvq_lvl_ind, stcsessd.geolocn_code, stmbiogr.surname, stmbiogr.forename, stmbiogr.birth_dt, stmadres.post_code,
stmaos.attend_mode, stmfesqa.fee_override, stmaos.start_date, RTRIM(stmfesqa.course_status) AS course_status, stmfesqa.course_status_dt as end_date,
stmfesqa.spec_learn_disab, FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age, delESOL.subj_code AS ESOL, delspurs.threshold1, /* Log No.132694 - SQL changed*/
delspurs.threshold2, delspurs.threshold3, stmfesqa.outcome, delbasicIT.qual_aim AS basicIT, ISNULL(delsubjarea.subj_area_weight, 0.00)
AS subj_area_weight, ISNULL(deloutwgt.weighting, 0.10) AS weighting, ISNULL(stmaos.additionality_yn, 0) AS additionality_yn,
nicisreports.dbo.ndaqquals.qual_ref
FROM stsacper INNER JOIN
stmaos INNER JOIN
stcsessd ON stmaos.aos_code = stcsessd.aos_code AND stmaos.acad_period = stcsessd.acad_period AND
stmaos.aos_period = stcsessd.aos_period INNER JOIN
stcfesdt ON stmaos.acad_period = stcfesdt.acad_period AND stmaos.aos_code = stcfesdt.aos_code AND
stmaos.aos_period = stcfesdt.aos_period INNER JOIN
stmfesqa ON stmaos.student_id = stmfesqa.student_id AND stmaos.aos_code = stmfesqa.aos_code AND
stmaos.acad_period = stmfesqa.acad_period AND stmaos.aos_period = stmfesqa.aos_period INNER JOIN
stmbiogr ON stmaos.student_id = stmbiogr.student_id ON stsacper.acad_period = stmaos.acad_period AND
stsacper.start_date <= stcsessd.aos_start_dt AND stsacper.end_date >= stcsessd.aos_end_dt INNER JOIN
delspurs ON stmaos.acad_period = delspurs.acad_period INNER JOIN
stcstatd ON stmaos.aos_code = stcstatd.aos_code LEFT OUTER JOIN
deloutwgt ON stcfesdt.nvq_lvl_ind = deloutwgt.nvq_lvl_ind LEFT OUTER JOIN
delbasicIT ON stcfesdt.qual_aim = delbasicIT.qual_aim AND stcfesdt.subject_code = delbasicIT.subj_code LEFT OUTER JOIN
delESOL ON stcfesdt.subject_code = delESOL.subj_code LEFT OUTER JOIN
stmadres ON stmbiogr.student_id = stmadres.student_id AND stmbiogr.perm_add_id = stmadres.add_id LEFT OUTER JOIN
delsubjarea RIGHT OUTER JOIN
delsubj ON delsubjarea.subj_area = delsubj.subj_area ON stcfesdt.subject_code = delsubj.subj_code left outer join
stcstdet on stmaos.aos_code = stcstdet.aos_code inner join
nicisreports.dbo.ndaqquals on replace(stcstdet.text_field1,'/','') = nicisreports.dbo.ndaqquals.qual_ref
where stmaos.acad_period = @p_acad_period
and (stmaos.return_ind = 'F' OR stmaos.return_ind = 'B')
AND (stmaos.stage_ind = 'E')
AND (stcfesdt.qual_aim IS not null)
AND (stcfesdt.qual_aim IS not null)
and (stmfesqa.fund_source <> '09' OR stmfesqa.fund_source IS NULL)
and stmaos.start_date is not null
and stmaos.start_date <= getdate()

OPEN @base
FETCH NEXT FROM @base
INTO @student_id, @acad_period, @aos_code, @aos_period, @full_desc_static, @full_desc_session,
@dept_code, @aos_start_dt, @exp_length, @unit_length, @student_year,
@hrs_per_week, @aos_end_dt, @no_of_weeks, @hrs_total,@student_type,
@moa_code, @qual_aim, @subject_code,@subject_area_group, @course_group,
@nvq_lvl_ind, @geolocn_code, @surname, @forename, @birth_dt, @post_code,
@student_attend_mode, @fee_waiver, @start_date, @student_status, @end_date,
@spec_learn_disab, @age, @ESOL, @threshold1, @threshold2, @threshold3, @outcome, @basicIT,
@subj_area_weight, @out_weight, @additionality_yn,@ndaq_qual

when i run the follwoing i get

exec testflu '06/07'

Server: Msg 16924, Level 16, State 1, Procedure testflu, Line 210
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

i am unsure cos from what i can see i ahve included all, am i missing something

View 7 Replies View Related

Need Help Wioth Variables In Stored Procedures

Mar 15, 2007

I'm working on my first stored procedure, having just watched a video about how to use them.  I have a working procedure that looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InMateSearch]    @State nvarchar(255)   AS       BEGIN    DECLARE @SqlString nvarchar(255)       SELECT [FIRST NAME], [LAST NAME], [STATE]     FROM members     WHERE suspend=0 AND (State=@State OR Country=@State)ENDWhat I need to do is build the WHERE clause of the SELECT Statement based on the values of the parameters passed in.  There will be a lot of them, but State will do for an example.I want to assign a variable, say SqlString  = " suspend=0".  Then I want to check the value of @State which is a parameter being passed in, then do something like this:If @State <> "" then SqlString = SqlString & " AND (State=@State OR Country=@State)" so that I'm only filtering by state if the state parameter isn't empty.  Then I would like my SELECT statement to look like: SELECT [FIRST NAME], [LAST NAME], [STATE]  FROM members  WHERE (SqlString)I'm declaring SqlString as the video indicated, but in the video the variable is assigned a value from the database.  I think I need a different kind of variable.  If I try to do something like SqlString = "test" or @SqlString = "test" I get an error.  Once I define or declare a variable I can use to build the string, how do I assign it to the WHERE clause?Diane  

View 3 Replies View Related







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