|
hi,
i want to fetch a data from the table which i have to give dynamically in my stored procedure
The thing I m stucked on is
---declaring a variable to hold table name
declare @TableName nvarchar(50)
--here i m setting the tablename for example my actual situation it will come from . result of queries prior to this
@Tablename='Annotationproperty'
--Select particular column from particular table for this row
select propertyID from @TableName where UserID=1
It gives an error that Must declare the table variable "@TableName"
So how I can give a dynamic table name in my sql query or is there some alternate way for this.
waiting ur replies
thanx
sandeep
|
|
|
|
|
As you have found, you cannot use a parameter as a table name. You need to build a dynamic SQL string and execute it.
You can do this:
DECLARE @sql nvarchar(4000);
SET @sql = 'SELECT * FROM ['+@TableName+']';
EXEC(@aSQLstring)
So, you need to build a string containing the whole SQL Statement and then execute it. However, be aware that this is prone to mallicious attack if you are not careful. Always verify that the contents of @TableName actually contain the name of a table in your database before performing an EXEC(@sql) by doing something like this
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName)
BEGIN
-- Put your code here
END
ELSE
BEGIN
-- Put your error handling code here. The table does not exist!
END
Finally, declaring @TableName as varchar(50) is potentially going to cause errors. If you are holding the table name in the variable declare it at sysname or nvarchar(128), e.g.
DECLARE @TableName sysname
Does this help?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
hi
Colin Angus Mackay
Can u please help me in my other problem too. I m doing as u told , now i want to store the result of the exec (@sqlQuery) into a variable @TempColumnValue of type nvarchar(100)
Note: In my case exec (@sqlQuery) always returns a single value
example:
declare @sqlquery varchar(100)
declare @TempColumnValue nvarchar(50)
set @sqlQuery = 'select @TempColumnValue = firstname from usertable where userid=1'
exec (@sqlQuery)
but this gives error 'Must declare the scalar variable "@TempColumnValue"'.
Is there any other way to get it done
waiting ur response!
thanx
sandeep
|
|
|
|
|
Yes, but it is an awkward solution.
You need to insert the result of the EXEC(@sqlQuery) into a temporary table, then get the value from the table
INSERT #MyTempTable EXEC(@sqlQuery);
SELECT @TempColumnValue = MyTempColumn FROM #MyTempTable
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi.
I am jawwad Khan From Pakistan.
I am using visual basic.NET 2005
While programming the database we were used to use the Filter property of the Recordset to select the records satisfying certain criteria.
i.e
dim rs as recordset<br />
.....<br />
......<br />
rs.filter="CustomerID=" & textbox1.text
suppose we have a datagridview showing data from a certain dataset. How can we filter the dataset and show the data in the datagrid.
i am not using the datasource or any binding control.
i want every thing to be done through code.
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
thank you
|
|
|
|
|
What is the code in VB.NEt to retrieve data from ms-access
Thanks
|
|
|
|
|
|
hi all,
I have created DB using SQL Server 2000.
In a Table there is autoNumber field.
Now I need to set a Value in that auto Number Field.
i.e-->I need to set 1 in that Field.
How do I do it using a query?
if any one know it pl kindly reply asap.
thanks in advance
|
|
|
|
|
Run this query in QA
insert into Customers(customerid ,companyname)values(@@identity,'Exensys')
"I find that the harder I work, the more luck I seem to have."
|
|
|
|
|
 I need to make UPdate stored procedure...
like this..
create proc update_user
(@user_name nvarchar ,@password nvarchar, @firstname nvarchar ,@lastname nvarchar)
update Users
set [password]=@password
(but if @password=empty string then keep no change in db column "password" )
and update other fields ..
thanks
|
|
|
|
|
Retrieving old password and keep it in a variable or session.
use the condition stmt in the asp.net. For eg:
if txtpass.text="" then
cmd.parameter.add("@password",session("pass"))
else
cmd.parameter.add("@password",txtpass.text)
end if
Hope this will help to solve your prob.
|
|
|
|
|
Simple way:
create procedure update_user
@user_name nvarchar(20),
@password nvarchar(20),
@firstname nvarchar(20),
@lastname nvarchar(20)
as
if @password is null or @password=''
update Users set firstname=@firstname, lastname=@lastname where user_name=@username
else
update Users set firstname=@firstname, lastname=@lastname, password=@password where user_name=@username
|
|
|
|
|
if @password = '' set @password = null
update Users
set [password] = isnull(@password, [password])
etc.
|
|
|
|
|
When using the "Data Source Configuration Wizard" you eventually get to a "Add Connection" dialog box. It has a drop-down box for "Server Name". I do not get any choices for the server name - why not? Next to the "Server Name" there is a "Refresh" button, but this does not help either.
Note - I have both sqlexpress and mssqlserver database engines opened. The micrsoft help does state: "Local SQL Server Express instances are not listed in the listing of available data sources." So I would at least expect my MSSQLSERVER database engine to appear?
|
|
|
|
|
Which ADO version first became a ".NET" version? And what is the current version of ADO.NET called - is it ADO version 2.8?
-- modified at 12:42 Wednesday 15th March, 2006
|
|
|
|
|
ADO.NET started with version 1.0 - There is not a direct upgrade path as the two are separate products that do more or less the same thing. Classic ADO is no longer updated.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
3/17/06
Please correct me if I am wrong on the following (summarizes ADO versions vs ADO.NET versions, and response from Colin above!)
* Most importantly: ADO and ADO.NET are different products, ADO.NET is not an upgrade of ADO. This can be confusing because they both have "ADO" in the name, but they are still different products.
* The ADO.NET product is not an upgrade in version numbers from the ADO product.
* ADO latest version seems to be 2.8 and ADO will no longer be updated. A book on ADO 2.5 is of limited value for using ADO.NET!
* ADO.NET versions correspond to .NET framework versions? For example .NET 1.0, 1.1, 2.0 are the more-major versions of .NET that have been released.
* ADO is often referred to as classic ADO
|
|
|
|
|
Hi all
Does anyone has good tutorial for SQL Muiltiple Join
chandana
|
|
|
|
|
|
|
|
How to backup data from Online WebServer to My local Server
I am using SQL Server 2000.
N.Rajakumar B.E.,
Application Developer,
www.newdreamz.tk
|
|
|
|
|
Hello,
I am using SQL Server 2005 and I am just wondering if I can build the WHERE clause dynamically. I only want to build WHERE class if the my @VIN count is of exactly 17 charecters. Here is my code
*************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
if count(@vin) = 17
begin
Where VIN = @VIn
end
END
*******************************************************
But when I parse the above stored proc its throwing this message
********
Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line 33
Incorrect syntax near the keyword 'Where'.
********
If it is possible to dynamically build WHERE clause please correct the way I am doing it or if it is not possible what is the best way of writing it?
Thanks
-L
|
|
|
|
|
This will seem silly but:
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
if count(@vin) = 17
begin
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
Where VIN = @VIn
end
ELSE
begin
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
end
You can take a look at sp_executesql to see about building SQL query strings on the fly for more difficult dynamic queries.
|
|
|
|
|
Hi Michael,
No worrys! All we do is try and keep trying until we get it I appreciate you reponse. Yeah upto a little ago I have done the same way to manage the show. But later this is how I did, I just tried building where clause dynamically and it works! May be you want to try if you come across with such scenarios.
WHERE VIN = CASE LEN(@vin) WHEN 17 THEN @Vin ELSE VIN END
Thanks for the reply.
-L
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
