|
how can we put variable inside the query i put it as
'select '+@name+' =hlp_tTblNm from mhelp with(nolock) where hlp_tAlias='acc2''
but it shows problem
shailendra
|
|
|
|
|
No, this won't work because the result of your varible gets placed inside the query and not the variable reference. If you are going to use a variable inside a dynamic t-sql string that variable must be declared inside the actual t-sql string.
Plus, if you're using your select statement to instantiate the variable @name is probably null. Which means if CONCAT_NULL_YIELDS_NULL is set to the default your entire string will be null.
If you can provide context for what it is you're trying to do we can give a recommendation that will help. Right now there are too many possibilites.
But I will at least take an initial jab and say that you're trying to do something like this:
<br />
DECLARE @Sql VARCHAR(2000)<br />
<br />
SET @Sql = '<br />
DECLARE @name = VARCHAR(50)<br />
SELECT @name = hlp_tTblNm FROM mhelp WITH(NOLOCK) WHERE hlp_tAlias = 'acc2'<br />
<br />
SELECT .... FROM ??? WHERE ??? = @name'<br />
<br />
EXECUTE (@Sql)<br />
|
|
|
|
|
I have to take the value of a column in a variable @name
The below code works fine but i have to take it in another variable @query
but it doesn't works please suggest me the solution
declare @name varchar(50)
declare @query varchar(500)
select @name=hlp_tTblNm from mhelp with(nolock) where hlp_tAlias='acc2'
print @name
problem
*********
declare @name varchar(50)
declare @query varchar(500)
set @query='select '+@name+'=hlp_tTblNm from mhelp with(nolock) where hlp_tAlias='acc2''
print @name
print @query
this code give me error incorrect syntex near acc2
shailendra
|
|
|
|
|
You need to put two quotes in before acc2, e.g. ''acc2''.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am getting a weird datetime error trying to insert a System.DateTime into a database.
The code i have used is:
cmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value = DOB.Date.ToShortDateString();
where the string returned is 24/11/1972 and also used the format 11/24/1972.
I have also created an SlqDateTime and passed in my System.DateTime object to the constructor then used this code
cmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value = sqlDate;
But every time i catch this exception.
"Conversion failed when converting datetime from character string."
I am pretty confident this is a school boy error so any help would be much appreciated.
Cheers
Dan
|
|
|
|
|
DanB1983 wrote: cmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value = DOB.Date.ToShortDateString();
Try this:
cmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value = DOB.Date;
(I'm assuming that DOB.Date is a System.DateTime object.)
Jon Sagara
I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So i had to leave the place as soon as possible.
--Mr.Prakash
Blog | Site | Articles
|
|
|
|
|
Hi
I am using C++ and ADO to connect and retrieve data out of a MSSQL database (2005). I have a problem when executing simple queries like "SELECT MyText From MyTable" (whereas MyText would be a VARCHAR). For executing this query I use a record set like this:
RecordSet->Open(...);
if (!RecordSet->EndOfFile) {
_bstr_t result = RecordSet->Fields->GetItem(_T("MyText"))->GetValue();
...
}
After this, the result variable contains in fact a value for MyText in this table. But the problem is that it does not only contain the text of MyText but also many whitespaces behind. That means, that if MyText was e.g. a VARCHAR(100), I get the text of it and behind a sequence of spaces till my _bstr_t gets the length of 100.
Is there any solution to this problem, except for parsing out the spaces behind my string by hand?
|
|
|
|
|
Hi,
I use _variant_t to get the value _variant_t tValue = pRS->GetFields()->GetItem(_T("YouFieldName"))->GetValue(); it works well, if you want to get CString type data, then: tValue.ChangeType(VT_BSTR);CString strValue = tValue.bstrVal;
|
|
|
|
|
Hi,
thanks for your reply. I did a mistake when posting the code in here. In fact I do it exactly the way you do it. But I have still this problem that there are a lot of spaces behind the "real" string. Any other ideas? I don't have a real idea.; it's just very strange...
|
|
|
|
|
Hi,
It's very strange, i use mssql2000+sp4 and not found this trouble.
why didn't you using strValue.trimRight() to trim the space off?
|
|
|
|
|
Yes that's of course a possibility, which means that I parse out the spaces behind the real string by hand. But I wondered if there wasn't any other possibility or if I made something wrong when getting this value from the database. But it seems that I made it correctly... that's just strange...
Anyway thank you
|
|
|
|
|
Hey.
I am currently working on my database for users of a site I am building. I have a stored procedure that takes parameters to fill in a users table - these are email, password and group. I then add the users credentials to a table depending on their group. I am using transactions so if there are any failures I can roll back.
My problem is that i set up an sqlCommand and add parameters to it, i then wish to use this same command but supply different parameters for the next stored procedure but I am told the paramater list is too large (makes sense) so do I remove the parameters supplied first? and if so how?
I am open to suggestions of different methods and their benefits.
Cheers
Dan
|
|
|
|
|
It's ok fixed it just used sqlparamter::clear() - silly error
|
|
|
|
|
Hi all,
i just want to know how can we programatically increase or decrease the number of parameters of a stored procedure that is stored in a DB.Let me explain it first
I am reading some distinct values from a table column and depending upon the rows returned i have to set the parameters of Stored proc accordingly.The table i am reading from can contain any no of tuples.
Help!!!
|
|
|
|
|
As far as I know you'd have to alter the proc to change it's parameters and you could do this programatically but at that point you might as well just do dynamic sql. Could you use a comma seperated list of values as parameters? Do have some example code for the stored procedure and what it's supposed to do?
|
|
|
|
|
The database is on server(sql server 2000).
The application (Windows application c#) run on the client pc's.
How can i update the data in the application when the data on server has been changed?
|
|
|
|
|
|
The database is on server(sql server 2000).
The application (Windows application c#) run on the client pc's.
How to use the SqlTriggerContext to update the data in the application when data on server has been changed?
|
|
|
|
|
Hi
can anyone plz tell me the query to access all the attribute names of one table only.
i'm working in Sql Server.
Saira
|
|
|
|
|
try this:
SELECT C.Name
FROM sysobjects O
JOIN syscolumns C ON O.ID = C.ID
WHERE O.Name = 'YOUR_TABLE_NAME'
|
|
|
|
|
select * from
or
select <field names=""> from <able name="">
eg
select * from emp
select empno,ampname,sal from emp
|
|
|
|
|
I use ExecuteReader to perform "SELECT * FROM tablename WHERE 0=1"
then I iterate across the fields returned in the DataReader.
You get names and datatypes, plus you can access both the .net datatypes and the internal datatypes.
Close the DataReader _after_ accessing the information.
This works for any data provider, not just SQL Server.
|
|
|
|
|
Hi ,
I had some problem with my select query.Let me explain you whole.
I had created dynamic query in my select query as i required performance.
My Select query is as below:
Select firstname,lastname,dob,ssn from person
where
(
firstname like 'abc%' and lastname like 'xyz%'
OR
firstname like 'ypx%' and lastname like 'por%'
OR
firstname like 'stup%' and lastname like 'rob%'
)
Now here i required one more field which is not available in
database table (person table).
Now that field is MatchType.Matchtype is used for
If user input of firstname and lastname is exactly matching with
database record then i have to write matchtype ='Exact Match'
if user input of first 3 character of firstname and lastname is matching
then i have to write Matchtype ='Potiential Match'
Now problem is that :
If i will write subquery like
Select firstname,lastname,dob,ssn,
(
Select
//some login which return Match type
) as Matchtype
from person
where
(
firstname like 'abc%' and lastname like 'xyz%'
OR
firstname like 'ypx%' and lastname like 'por%'
OR
firstname like 'stup%' and lastname like 'rob%'
)
If i can't write query like that becoz subquery should return only one
value.
so can somebody help me out here.??
If you have any question then let me know without hesitation.
Thankx a lot in advance.
regd,
montu3377
|
|
|
|
|
I'm not sure if I totally understand your requirements but would the following work.
Select
firstname
,lastname
,dob
,ssn
,case when (firstName = 'abc' and lastname ='xyz') or (firstname ='ypx' and lastname ='por') or (firstname ='stup' and lastname ='rob') then 'Exact Match'
else 'Potential Match'
end as [MatchType]
from person
where
(
firstname like 'abc%' and lastname like 'xyz%'
OR
firstname like 'ypx%' and lastname like 'por%'
OR
firstname like 'stup%' and lastname like 'rob%'
)
|
|
|
|
|
I need to migrate about a dozen databases from SQL Server 2005 running on (ComputerAboutToBarf01) to (ServerAboutToSaveTheDay01). Do I have to manually backup then restore each database or is there a utility out there that will do this for me? I tried it in the Management Studio but don't seem to see any point-and-click way to do it all at once.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
