Using A Variable For Tablename In Select Statement?
Sep 19, 2007
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!
View 1 Replies
ADVERTISEMENT
Oct 12, 2005
I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.
I then need to run the following script:
declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table
The message I receive is "must declare variable @table".
Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.
Can anyone let me know what I'm doing wrong or how I can get the same result.
Thanks
View 4 Replies
View Related
Feb 7, 2006
Dear all,
Can someone help me with the following function? I would like to use a table name as a variable.
Thanks in advance!
CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric
if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end
if @categorie = 'open_te_laat'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end
end
return @aantal
END
View 2 Replies
View Related
Jul 1, 1999
I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt
thanks in advance
View 1 Replies
View Related
Oct 4, 2006
I am trying to set a vaiable from a select statement
DECLARE @VALUE_KEEP NVARCHAR(120),
@COLUMN_NAME NVARCHAR(120)
SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')
SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)
PRINT @VALUE_KEEP
PRINT @COLUMN_NAME
RESULTS
-------------------------------------------------------------------------------------------
FirstName <-----------@VALUE_KEEP
FirstName <-----------@COLUMN_NAME
SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName
SELECT FirstName from Contacts returns: Brent
How do I make this select statement work using the @COLUMN_NAME variable?
Any help greatly appreciated!
View 2 Replies
View Related
Aug 28, 2007
We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
View 3 Replies
View Related
May 6, 2015
I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.
So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROM ? ......
Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:
[Task Execute SQL] Error:
Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract
the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.
how to pass a tablename as a variable to a query?
View 5 Replies
View Related
Sep 6, 2006
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.
select @column_name from table
View 2 Replies
View Related
Apr 23, 2007
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20))
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ProjectNumber int
SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','')
SELECT MAX(@ProjectNumber)
FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','');
END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
View 3 Replies
View Related
Apr 16, 2008
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
View 1 Replies
View Related
Dec 7, 2005
this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on ((( @UsersMaxID ))) but for some reason it will not work with the next select statement...
can someone make the pain go away and help me here..??
erik..
GOSET ANSI_NULLS ON GO
ALTER PROCEDURE AA
ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200)
SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik'
DECLARE @SQL NVARCHAR(4000) DECLARE @UserID INT DECLARE @UsersMaxID INT DECLARE @MaxID INT
declare @tempResult varchar (1000)
-------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY,
UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)
Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr
FROM USERS
WHERE ' + @GenericColumn +' = ''' + @GenericValue + ''''
EXEC sp_executesql @SQL
SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29
SELECT * FROM #UsersTempTable
==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE
View 1 Replies
View Related
Dec 28, 2004
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.
What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).
This is the code in my procedure..
DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list
DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt
select count(*) from @tab_name
This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.
Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.
Please help me to construct the select statement that is similiar to
x=<table name>
select * from x
where x is a variable and the table name gets substituted.
what is the syntax for it ?
View 7 Replies
View Related
Nov 10, 2006
How do I use a variable to specify the column name in a select statement?
declare @columnName <type>
set @columnName='ID'
select @columnName from Table1
View 8 Replies
View Related
Sep 23, 2006
Is it possible to add a variable in SSIS like
name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])
@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made
I know I'm doing something wrong with the data type because it's stores the whole select statement as a string
Help
Worf
View 8 Replies
View Related
Dec 19, 2003
Example:
Select icount + 1 as icount from table
or
Select counter() as icount from table
The above is wrong ...just a sample to show what I am trying to accomplish.
Is there a function in SQL statement? Thanks.
View 2 Replies
View Related
Mar 9, 2008
hey all,
I have the following query:
ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]
AS
BEGIN
DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)
DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)
SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count
FROM article
JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id
GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name
HAVING article_id = @article_id
END
GO
as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.
I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.
But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.
And i cant add a join, because it would return the amount of rows of the comment...
unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?
Cheers
View 6 Replies
View Related
Nov 12, 2013
I am trying to figure out a way to retrieve a field value and assign it to a local variable with out destroying the whole structure of my T-SQL statement.
Here is the code:
DECLARE @AVERAGE_WHOLESALE_PRICE VARCHAR(20)
DECLARE @ORDERBY VARCHAR(20)
SELECT TOP 1 @AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX,
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
[Code] ....
The error message is
Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
How to modify this statement?
View 8 Replies
View Related
Feb 11, 2008
I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:
SET @value = select max(price) from product
but this does not work, can someone tell me how I would go
about in storing the results in a variable.
@value is declared as int
Thanks in advance,
Sharp_At_C
View 1 Replies
View Related
May 21, 2006
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)
@a = Select * from foo ---Obviously mine is much more robust
How do I execute @a to actually Select * from foo?
View 3 Replies
View Related
Apr 21, 2015
Everything about this query works except I'm trying to capture the @companyid (which is a variable) into a column in my table via my select statement.
My error is Invalid column name 'A113', etc. However it is the A113 I'm trying to insert into the first column of the table SAP_GLsummary
-- retrieves a list of gl balances from all companies
truncate table sap_glsummary
declare @companyID char(6)
declare c_company cursor for
select INTERID from dbo.GP_Interid
open c_company fetch next from c_company into @companyID
[Code] .....
View 4 Replies
View Related
May 7, 2008
In my program i have function that will get one value from Database.
Here i want to assign the output of the sql query to a local variable.
Its like select emp_id into Num from emp where emp_roll=222;
here NUM is local variable which was declared in my program.
Is it correct.?
can anyone please guide me..?
View 7 Replies
View Related
May 6, 2015
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
View 4 Replies
View Related
Apr 24, 2007
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
View 5 Replies
View Related
Apr 4, 2008
Hi to all , I write the query like belwodeclare @userid nvarchar(20)set @userid ='6,8'print @useridselect * from user_master where user_id in (@userid)Here user_id is int datatype, when i execute this query then meet the error as 6,8Msg 245, Level 16, State 1, Line 4Syntax error converting the nvarchar value '6,8' to a column of data type int. How to do this.? Anybody answer me,...
View 5 Replies
View Related
Feb 19, 2001
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go
insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column*/
set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.
this error returned in second
View 1 Replies
View Related
Feb 19, 2001
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go
insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column
note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/
set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.
this error returned because max(id_no) column is absent
if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2
this query return that
insqlhelp ---------------
aaa2
(1 row(s) affected)
this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")
other way is
set to any other text variable to table name,
and concetanate to query,
and execute with exec
but in this way you cant set to my int variable
please help me thanks for all
hra
View 1 Replies
View Related
Nov 6, 2007
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
View 1 Replies
View Related
Mar 28, 2000
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list
I have difficulty combining a select statement with a where clause stored in a variable inside a cursor
The users select the mail content and frequency of delivery and i deliver the mail
I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.
Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work
For example
DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3
I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary
How do I combine the select statement with the where clause?
Help me...I need help urgently
View 1 Replies
View Related
May 26, 2015
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;
View 11 Replies
View Related
Aug 30, 2000
Hi all.
I want something like this :
create proc myProc ( @num int ,@name varchar(80))
as
select @num = max(field1) from @name
but there is a problem that the @name is a varchar and the error is :
Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3
Line 3: Incorrect syntax near '@name'.
I tried
select @num = max(field1) from object_id(@name)
and the error was the same
is ther another way than :
exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ?
thx
Eyal Peleg
View 1 Replies
View Related
Nov 7, 2007
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
View 14 Replies
View Related
Nov 5, 2007
I am learing VWD as I build a site and while I can use the various controls (GridView, FormView) to set up operations and retrieve/display database information, I need to be able to codebehind to do SQL operations for retrieving data to fill paper forms and to modify the database. After I retrieve data from the database, I need to do a lot of computation befor I build a paper quote that is emailed to a customer. I have written a subroutine to which I pass my sql query statement and I can add a new entry and edit an existing entry. The problem is a simple query to read an existing database entry.
If I write a simple command : Cmd = SELECT PartNumber, PartName, PartsAvailable FROM Inventory WHERE PartNumber = "P7226" , the command is executed but the return data falls on the floor somewhere as I dont know how to code to put the results in an array or in fact, have any idea on how to find the returned data. Can anyone help with this simple(?) problem? My sub that handles the sql command is shown below.
I have also been looking for the way to code in VB to cause existing controls like GridViews to access, edit, and insert but I havent been able to find a paper or tutorial. If anyone has seen this information, it would be helpful also.Protected Sub CallSQL(ByVal Cmd As String)
Dim conn As New SqlConnection("Data Source=.SQLExpress;AttachDbFileName=C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True") 'Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True)Dim sql As String = String.Empty
sql = Cmd
'Open the connection to the database and execute he SQL
conn.Open()Dim command As New SqlCommand(sql, conn)
command.ExecuteNonQuery()
conn.Close()
End Sub
View 6 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related