Dynamic Build SQL In Store Procedure Based On Select

Jul 23, 2005

I have a department table like this:
DeptID Department ParentID, Lineage
1 HR NULL (
2 Temp1 1 (1,
3 Temp2 2 (1,2
4 PC NULL (

I have a deptmember table like this:
DeptID MemberID IsManager
1 1 Y
4 1 Y

I need to query table to get all department belong to MemberID 1 with
all children departments.

My thought is:
1. Do Select * from deptmember where MemberID=1 and IsManager=Y
2. Loop thru this table to build SQL
Where Lineage like '%1' OR Lineage like '%4'
3. Select * from department using where statement from step 2.

How do you loop thru results from step1, Do I need to use a cursor?

Thanks,

HL

View 3 Replies


ADVERTISEMENT

Trying To Build Dynamic Stored Procedure

Jun 6, 2008

My existing ASP 1.0 site keeps getting hacked using SQL injections.  I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search.
I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck.  Getting either errors or double LIKE statements. Need some help.
string[] SqlKWSrch; 
SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ',');     int AStop = SqlKWSrch.Length;     int i = 0;        foreach( string a in SqlKWSearch )       {           if (i <= AStop)           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND ";           }           else           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' ";           }            i++;       }
1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure
GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion.
PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1
RETURN
 
Thank you
 

View 5 Replies View Related

How To Build A Procedure That Returns Different Numbers Of Columns As A Result Based On A Parameter

Nov 23, 2006

/*Subject: How to build a procedure that returns differentnumbers of columns as a result based on a parameter.You can copy/paste this whole post in SQL Query Analyzeror Management Studio and run it once you've made surethere is no harmful code.Currently we have several stored procedures which finalresult is a select with several joins that returns manycolumns (150 in one case, maybe around 50 the average).We have analyzed our application and found out that mostof the time not all the columns are used. We haveidentified 3 different sets of columns needed indifferent parts of the application.Let's identify and name these sets as:1- simple set, return the employee list for example2- common set, return the employee information (whichinclude the simple set)3- extended set, return the employee information (whichinlude the common set which itself includes the simpleset) + additional information from other tables, maybeeven some SUM aggregates and so on (I don't know forexample, how much sales the employee did so far).So the bigger sets contain the smaller ones. Please keepreading all the way to the bottom to better understandtechnically what we are trying.Here is a code sample of how our current procedureswork. Please note that the passing parameter we can eitherpass a Unique Identifier (PK) to retrieve a single record,or if we pass for example -1 or NULL we retrieve all theemployee records.*/create table a ( apk int primary key, af1 int, af2 int, af3 int, af4int, af5 int, af6 int)create table b ( bpk int primary key, bf1 int, bf2 int, bf3 int, bf4int, bf5 int, bf6 int)create table c ( cpk int primary key, cf1 int, cf2 int, cf3 int, cf4int, cf5 int, cf6 int)create table d ( dpk int primary key, df1 int, df2 int, df3 int, df4int, df5 int, df6 int)insert a values (1,1111,1112,1113,1114,1115,1116)insert a values (2,1211,1212,1213,1214,1215,1216)insert a values (3,1311,1312,1313,1314,1315,1316)insert a values (4,1411,1412,1413,1431,1415,1416)insert a values (5,1511,1512,1513,1514,1515,1516)insert a values (6,1611,1612,1613,1614,1615,1616)insert b values (1,2111,2112,2113,2114,2115,2116)insert b values (2,2211,2212,2213,2214,2215,2216)insert b values (3,2311,2312,2313,2314,2315,2316)insert b values (4,2411,2412,2413,2431,2415,2416)insert b values (5,2511,2512,2513,2514,2515,2516)insert b values (6,2611,2612,2613,2614,2615,2616)insert c values (1,3111,3112,3113,3114,3115,3116)insert c values (2,3211,3212,3213,3214,3215,3216)insert c values (3,3311,3312,3313,3314,3315,3316)insert c values (4,3411,3412,3413,3431,3415,3416)insert c values (5,3511,3512,3513,3514,3515,3516)insert c values (6,3611,3612,3613,3614,3615,3616)insert d values (1,4111,4112,4113,4114,4115,4116)insert d values (2,4211,4212,4213,4214,4215,4216)insert d values (3,4311,4312,4313,4314,4315,4316)insert d values (4,4411,4412,4413,4431,4415,4416)insert d values (5,4511,4512,4513,4514,4515,4516)insert d values (6,4611,4612,4613,4614,4615,4616)gocreate procedure original_proc @pk int asif @pk = -1set @pk = nullselecta.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2, b.bf3, b.bf4, c.cf1, c.cf2,c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4fromajoin b on a.apk = b.bpkjoin c on b.bpk = c.cpkjoin d on c.cpk = d.dpkwherea.apk = ISNULL(@pk, a.apk)goexec original_proc 1go/*Currently the above SP is a single SP that is basicallyreturning ALL possible needed data. However most of thetime we might need to call and retrieve a simple employeelist.So we thought about modifying the stored procedure byadding an extra parameter that will indicate which setof columns to return.For modifying the stored procedure in order to get avariable name of columns returned and avoidingrepeating code, we built 4 objects: the storedprocedure being called, one table function and 2 views.One table function so that we are able to pass a parameter.The views since they do not accept parameters they arealways joined at least with the inline table function.The stored procedure generates in its body a dynamicSQL statement, where it queries the table function andthe views, depending which set is required. Here is acode sample of our current design (you need to run theprevious code in order for this to work).*/create function _1_set(@pk int)returns tableas return(select a.apk, a.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2from ajoin b on a.apk = b.bpkwhere a.apk = ISNULL(@pk, a.apk))gocreate view _2_set asselect b.bpk, b.bf3, b.bf4, c.cf1, c.cf2from bjoin c on b.bpk = c.cpkgocreate view _3_set asselect c.cpk, c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4from cjoin d on c.cpk = d.dpkgocreate procedure new_proc @pk int, @set int asdeclare @sql nvarchar(4000)if @pk = -1set @pk = nullset @sql = 'select * from _1_set(@pk) fs 'if @set 1set @sql = @sql + 'join _2_set ss on fs.apk = ss.bpk 'if @set 2set @sql = @sql + 'join _3_set ts on ss.bpk = ts.cpk 'exec sp_executesql @sql, N'@pk int', @pkgoexec new_proc 1, 3go/*For executing the new procedure, we pass parameter 1for the smaller set, 2 for the medium size set or 3for the complete set.For example when we want to retrieve the common setwe pass the Unique Identifier of the employee to theSP and then we pass the type of set we want to useas the second parameter (1 for simple set, 2 forcommon set and 3 for extended set).The SP has the IF and dynamic SQL to add more JOINs.We would like to know what you think of this approachand if you know a simpler way of doing it.For cleaning up the test objects run the following code.*/drop procedure original_procdrop procedure new_procdrop function _1_setdrop view _2_setdrop view _3_setdrop table adrop table bdrop table cdrop table dAs always I would appreciate any feedback, opinion,comments, ideas and suggestions.Thank you

View 9 Replies View Related

Dynamic Query With Variables In Store Procedure

May 18, 1999

Hi!
Here is a snap form my code

declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")

I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"

Can someone help me on how to assign a value to a local variable from a dynamic query.

Thank for any help in advance
Hank Lee

View 1 Replies View Related

How To Select In From A Store Procedure Result?

Feb 26, 2007

HI, I'm a simple store procedure that returns a result such as this one:

AM
AM-1
AM-2
AM-n

and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile

select fields from table where id in (execute sp)

how can I make this?

Thanks a lot.

View 4 Replies View Related

EXECUTING STORE PROCEDURE IN SELECT STATMENT

Aug 27, 2001

hI
DOES ANY BODY KNOW HOW TO EXECUTE STORE PROCEDURE IN SELECT STATMENT

THANKS
jk

View 1 Replies View Related

Print A Select Statement Within A Store Procedure

May 8, 2008

I want to create store procedure which will print out something like this:

Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn)
But first one print out good, as I expected. I either got nothing or the error message on second piece.

Msg 245, Level 16, State 1, Line xxx
Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.
Any Help will be greatly appreciated.

on SQL 2005 SP1.
Here is my example code (not the real one):
Create Procedure [dbo].[makeNewString]
@myBookID
As
Declare bookID int
Declare newRowID int
Declare insertBookInfoString nvarchar(150)
select 'Declare @newBookID int'
Set insertBookInforString =
(Select 'Insert into [dbname].dbo.[gameBooks] value (' + @gameBookID + ',' + gameBookTitle +',' + ganeVolumn +')'
from [dbname].dbo.[gameBooks])
where @gameBookID=@myBookID
Print insertBookInforString
select 'SET @newRowID = Scope_Identity()'
print @newRowID
select 'Declare @newBookID int'
Set insertBookInforString =
(Select 'Insert into [dbname].dbo.[cookBooks] value (' + @cookBookID +',' + cookBookTitle +','+cookVolumn+')'
from [dbname].dbo.[cookBooks]
where @cookBookID=@myBookID)
Print insertBookInforString
first insertBookInfoString prints out fine.
But I kept this error on second part:

------------------------------
DECLARE Declare @newBookID int
(1 row(s) affected)
Insert into [dbname].dbo.[gameBooks] value (@gameBookID, gameBookTitle, ganeVolumn) (this is what I want)
-----------------------------------------
SET @newBookID = Scope_Identity()
(1 row(s) affected)

Msg 245, Level 16, State 1, Line xxx
Conversion failed when converting the varchar value 'Insert into [dbname].dbo.[cookBooks] value (' to data type int.

View 1 Replies View Related

Will A Store Procedure Execute Faster Than Regular Select ?

Nov 7, 2003

Hello,

Lets say I have a SP that return 1000 records,

do I get any better speed if doing it on a SP instead of just SELECT without an SP ?

if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?


(Why should I use SP if not passing any parameters ?)

View 7 Replies View Related

SQL 2012 :: Store Procedure Only Output One Select Statement

May 28, 2014

There are about 10 select statements in a store procedure.

All select statements are need.

Is it possible to output only the result of last select statement?

View 2 Replies View Related

How Do You Use Data From A Select Statement As Inputs For A Store Procedure?

Dec 13, 2007

How do you use data from a select statement as inputs for a store procedure?

e.g.





Code Block

Select FirstName, LastName from Student where Grade = 'A'

And I want to use all the FirstName and LastName as inputs for this store procedure





Code Block

Exec StudentOfTheMonth @FirstName = FirstName, @LastName = LastName
Thanks

View 21 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Best Way To Build A Partitioned Clustered Column Store

May 19, 2014

I am building three partitioned, clustered column store tables.I was researching whether it was faster to populate a staging table and swap it into the partitioned table or to directly insert into the partitioned table.The first partition for the three tables will have:

Table F: 50M rows, 6 columns wide, partitioned on a date column (1 date, 2 bigint keys, and two varchar columns)
Table D1: 50M rows, 150 columns wide, partitioned on a bigint
Table D2: 19M rows, 300 columns wide, partitioned on a bigint

If build the data that would go into partition 1 in a non partitioned column store, I get these table sizes:

Table F: 476 MB
Table D1: 6,800 MB
Table D2: 5,496 MB

If build the same data directly in the partitioned column store, my table sizes end up being:

Table F: 579 MB
Table D1: 6,800 MB
Table D2: 5,364 MB

That's a 20% difference on Table F, the narrow table.Looking at the row groups, I see 47 identical row groups in partition 1 and the unpartitioned table, but the average "size_in_bytes" is consistently 20% smaller in the unpartitioned table.

View 2 Replies View Related

Build Hierarchy - Store Data Link In Table

Sep 3, 2013

I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this

Id-Hreid

Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get

Emp3-Emp2
Emp3-Emp1
Emp2-Emp1

View 2 Replies View Related

How Do You Build A Dynamic WHERE Statement?

Mar 9, 2006

I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext.
How would I go about building this efficiently?
Would I Declare a bit value in the sp called WhereSet = 0
IF @client IS NOT NULL     IF @WhereSet = 0          SET @Where = 'WHERE ClientID=@client'          SET @SetWhere = 1    ELSE          SET @Where = @Where + ' AND CleintID=@client'    .    .    ....
Or would this be a lot easier using adhoc SQL instead of a Stored Procedure?
(note: I am using a SQL DataSource)
Please help, I am going bald from pulling my hair our...

View 2 Replies View Related

Build Dynamic WHERE Clause

Apr 14, 2008

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'CNN')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'AOL')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'ABC')

Thanks for your help

View 2 Replies View Related

Build Dynamic WHERE Clause

Apr 14, 2008

Hello,

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyName = 'CNN'
AND CompanyName = 'AOL'
AND CompanyName = 'ABC'


P.S I know that above select doesn€™t really make sense , but I have a bigger query that would be hard to explain in this topic so I just simplified it.

Thank you

View 8 Replies View Related

Build Dynamic Query Using Sp_executesql

Sep 20, 2004

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

Thanks

View 1 Replies View Related

SQL Server 2014 :: How To Call Dynamic Query Stored Procedure In Select Statement

Jul 23, 2014

I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.

Now i want to call stored procedure in select statement because stored procedure return a single value.

I search on google and i find openrowset but this generate a meta data error

So how i can resolve it ???

View 7 Replies View Related

What Is The Best Way To Build The Search Based On Form Fields

Oct 5, 2007

I required to build the search feature for my application which contains combination of at least 20 search fields e.g firstname, lastname. date of birth, sign up date ,etc... I am just wondering what is the best way to do it ,should I create stored procedure with 20 input parameters or should I build it based on each search fields. I need to provide the search results via web services. Could anyone help me?
Thank you

View 5 Replies View Related

How Build Web Application (asp.net + C# ) With Dynamic Database Schema?

Mar 26, 2008

Hi all,

I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).

With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).

Therefore, I want to ask all of you that
- Is there anyway to solve this problem?
- how to design database that meet extended requirements as I present above?

Thanks for all of your advices.

Quan.

View 17 Replies View Related

Build SQL Query From Dynamic Checkbox List

Nov 26, 2003

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.

View 1 Replies View Related

Build Dynamic Table Columns Issue

Jun 4, 2004

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!

J827

View 4 Replies View Related

SQL Server 2012 :: Build Search Condition Dynamic

Oct 6, 2015

IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'

[code]....

i am trying to build a dynamic where "or" clause finding difficulties.

View 7 Replies View Related

Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database

Jul 17, 2015

I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.

SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''  in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)

View 9 Replies View Related

Select Query Vs Store Procedure Query

Oct 29, 1998

hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..

select * from authors

OR


create procedure authors as

select * from authors





lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input

Ali

View 1 Replies View Related

Need Help To Build A Select

Sep 29, 2004

Hi there, I have 3 columns that contain the same kind of information and I would like to do a select that would return all the distinct records of all 3 colums. Any help woulb be appreciated.
Thanks

View 4 Replies View Related

Cannot Build This Custom Select Statement...

Jul 20, 2006

I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this:
SELECT     filenameFROM         pictableWHERE     (@searchby LIKE @searchwords)
It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works:
SELECT     filenameFROM         pictableWHERE     (keywords LIKE @searchwords)
Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail.
Thanks in advance.

View 6 Replies View Related

Build Into One Procedure @@@@@

Jun 4, 2002

l would like to intergrate the the two procedures listed below into one procedure or function.
l'm doing it in two steps.This report will have each loan listed and a final line summed on each of the amounts?
enddate and startdate being my parameters and print the results to screen.And l'm failing to do that thats why its in two steps.Hope this makes it clear enough.



-- Exec New '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000'
-- Exec New1 '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000'


Alter procedure new (@startdate datetime,@enddate datetime)
as
begin

SELECT Distinct
Loan_no AS Loan_no,
Date_Issued AS Date_Issued,
Store AS Store,
Product AS Product,
Capital_Amount AS Capital_Amount,
Interest_Amount AS Interest_Amount,
Total_Amount AS Total_Amount,
Insurance_amount As Insurance_Amount,
Admin_Fee AS Admin_Fee,
User_Issued AS User_Issued,
LoanBook AS Company,
Status
FROM Loan
Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000'
End

Alter procedure new1(@startdate datetime,@enddate datetime)
As
Begin

SELECT
Sum(Capital_Amount) AS Capital_Amount,
Sum(Interest_Amount) AS Interest_Amount,
Sum(Total_Amount) AS Total_Amount,
Sum(Insurance_amount) As Insurance_Amount,
Sum(Admin_Fee) AS Admin_Fee
FROM Loan
Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000'

End

View 1 Replies View Related

How Can I Dynamically Build Sql SELECT Using ASP 1.0 Array Concept

Jun 6, 2007

The below code is ASP 1.0 to dynamically search a database and I want to use the same concept for a ASP.Net 2.0 solution.  Do I do this in the code behind or on the aspx page and if on the aspx page what controls do I use for the array split?  Basically where do I start.  It took me a long time to get this old code working, I am hoping it is simpler in 2.0.
Thank you
OLD ASP 1.0 code to dynamically build a Sql Select statement for searching a database using one or more search words entered by user.
If Request.Querystring("kwdSearch") <> "" ThenDim kwdString, ArrKwdString, iCountiCount = 0 kwdString = Replace(Request.Querystring("kwdSearch"), "'", "''")ArrKwdString = Trim(kwdString)ArrKwdString = Split(kwdString, " ",-1,1) For iCount = 0 to UBound(ArrKwdString) If iCount < UBound(ArrKwdString) Then  Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' AND "  Else  Criteria = Criteria & "tblLinkInfo.L_Keywords LIKE '%" & ArrKwdString(iCount) & "%' " End ifNext    RS.Open "SELECT * FROM tblLinkInfo Where (" & Criteria & ") AND L_Enabled = 1 ORDER BY " & SortBy & "L_Rank", CNN, 3 If RS.EOF Then  If Rs.State Then RS.Close  RS.Open "SELECT * FROM tblLinkInfo WHERE L_Description LIKE '%" & Replace(Request.Querystring("kwdSearch"),"''","'") & "%' AND  L_Enabled = 1  ORDER BY " & SortBy & "L_Rank", CNN, 3  End If
  RESULTS --- Display results with Repeater1.DataBind(); etc
  Exit SubEnd If

View 3 Replies View Related

Call Store Procedure From Another Store Procedure

Nov 13, 2006

I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:

I want to create a table and to insert the result of the store procedure to it.

This is the code: Pay attention to the underlined sentence!

ALTER PROCEDURE [dbo].[test]



AS

BEGIN

SET NOCOUNT ON;



DROP TABLE tbl1

CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )

INSERT INTO tbl1 (first_name,last_name)

VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')

END

PLEASE HELP!!!! and God will repay you in kind!

Thanks!

View 7 Replies View Related

SELECT With GROUP BY And Build A List From Vales Not Shown

Apr 16, 2004

Hard to write a subject line to describe this one.

Anayway, I have a table with names and address plus an extra field noting a part number of product.

I'd like to build a SELECT string that will return one result for each name/address (uniques only in other words) and build a comma delimited field of all the part numbers for that name/address.

Example:

NAME ADDRESS PART NUMBER
John Smith 555 Main st., los angeles, ca 90003 5000
John Smith 555 Main st., los angeles, ca 90003 6650
Mike Jones 8569 West 18th Ave., San Diego, ca 1255
John Smith 555 Main st., los angeles, ca 90003 5144
Mike Jones 8569 West 18th Ave., San Diego, ca 2399


So I'd like my results to look like this:

NAME ADDRESS PARTS
John Smith 555 Main st., los angeles, ca 90003 5000,6650,5144
Mike Jones 8569 West 18th Ave., San Diego, ca 1255,2399

THanks in advance for any suggestions!

Raul

View 2 Replies View Related

Need To Build A Search Stored Procedure

Feb 21, 2007

I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.
I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?
 

View 5 Replies View Related

Build String In Stored Procedure

Nov 19, 2007

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 ?

View 5 Replies View Related







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