How To Set A Variable In An If Exists Statement
Feb 28, 2008
Hello,
I would like to set a variable within my if exists statement, however SQL is throwing and error stating:
Incorrect syntax near '='.
If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable
Here is my code block:
Code Snippet
declare @permissionID int;
if exists(select @permissionID = Id from Permission
where [Description] = 'SettlementReport')
Thanks,
Flea#
View 3 Replies
ADVERTISEMENT
Jun 11, 2007
Is there a way to determine if a local variable exists or not?
There's a parameter I often use in code called @guid_batch that is usually declared in the parameter of a stored proc, but when in debugging it would be nice to have it available without having to change code.
Is there something that I could do similar to the following
Code:
IF VARIABLE_ID('@guid_batch') IS NULL
BEGIN
DECLARE @guid_batch UNIQUEIDENTIFIER
SELECT @guid_batch = NEWID()
END
Thanks in advance,
-MBirchmeier
View 8 Replies
View Related
Jul 20, 2005
Hi allIn the SP below im (trying to) do some dynamic sql. As you can see the tableto use is set as a variable and the 'exec' method used to run thesqlstatements.My problem is that the 'if exists' method is not doing what i was hoping itcould do.The @presql command returns somewhere between 0 or 50 rows (give and take) -i just want the 'if exists' part to determine if the select statementreturns something or not since i then will have to update a current row - orinsert a new one.Even if there is no rows returned, the 'if exists' command will return true:-/Any suggestions to a different way of approach...?Thanks in advance :-)######## Stored procedure start ########[various @ variables]....declare @presql varchar(200)select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as varchar(6))+''IF exists((@presql))BEGINdeclare @UpdateSQL varchar(400)set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate asvarchar(12))+''''exec(@UpdateSQL)ENDELSEBEGINdeclare @InsertSQL varchar(2000)select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '(btDate,btTime)VALUES('''+ cast(@FileDate as varchar(12)) + ''','+ cast(@ImportTime as varchar(6)) + ')'EXEC(@InsertSQL)END######## Stored procedure end ########
View 2 Replies
View Related
Feb 18, 2006
hi. i'm building a news section for some friends of mine. i list all the news items on the main page in a gridview. i've made a custom edit linkbutton that sends the user to an edit page, passing the news id as a quarystring variable. on the edit page i first check if the querystring variable contains an id at all. if not, i redirect the user to the main page. if an id is passed with the querystring, i fetch the matching news item from the database and place it in a formview control for editing.so far, so good. but what if someone types a random id in the querystring? then the formview won't show up and i'd look like a fool. :) therefore, i need some kind of check to see if the id exists in the database. if not redirect the user back to the main page... so i started thinking: i could check the databsae in a page_load procedure. if all is well, then display the news item. since the formview is automatically filled with the correct data, does that mean that i call the database two times? i mean, one for checking if the news item exists, and one for filling the formview. logically, this would be a waste of resources.help is appreciated.
View 3 Replies
View Related
Apr 5, 2007
I'm trying to gather some user statistics based on 3 conditions. First I want to check if the referring querystring is already in the database. If not insert it into the db. Second, if the querystring is already in the database, then check if the ip-address of the user is already in the database. If it is, then check if the ip address was inserted today. If not, update the "refCountIn" field with +1. The problem lies in the third condition where we check if the ip-address was inserted today and if false, update the "refCountIn" field with +1 and if true, do nothing.Below is the code I have until now: 1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
2
3 ' *** Declare the variables
4 Dim getStatCmd As String
5 Dim updStatCmd As String
6
7 Dim myRef As String = Request.QueryString("ref")
8 Dim myQueryString As String = Request.ServerVariables("QUERY_STRING").Replace("ref=", "")
9 Dim myRemoteAddr As String = Request.ServerVariables("REMOTE_ADDR")
10 Dim myHttpReferer As String = Request.ServerVariables("HTTP_REFERER")
11
12 Dim dtNow As Date = DateTime.Now
13 Dim dtToday As Date = DateTime.Today
14
15 ' *** Conditional INSERT command
16 getStatCmd = _
17 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime = '" & dtToday & "') " & _
18 "BEGIN " & _
19 "SELECT 'This clickin has already been recorded!'" & _
20 "END ELSE BEGIN " & _
21 "SELECT 'Clickin recorded' " & _
22 "INSERT INTO tblReferers(robotName, refIpAddress, refReferer, refTime) " & _
23 "VALUES(" & _
24 "'" + myQueryString + "'," & _
25 "'" + myRemoteAddr + "'," & _
26 "'" + myHttpReferer + "'," & _
27 "'" + dtToday + "')" & _
28 "END "
29
30
31 ' *** Conditional UPDATE command
32 updStatCmd = _
33 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime <> '" & dtToday & "') " & _
34 "UPDATE tblReferers " & _
35 "SET refCountIn = refCountIn + 1, refTime = '" & dtNow & "' " & _
36 "WHERE refIpAddress = '" & myRemoteAddr & "' AND robotName = '" & myRef & "'"
37
38 Dim insConnCmd As New SqlCommand(getStatCmd, New SqlConnection(connSD))
39 Dim updConnCmd As New SqlCommand(updStatCmd, New SqlConnection(connSD))
40
41 insConnCmd.Connection.Open()
42 insConnCmd.ExecuteNonQuery()
43 insConnCmd.Connection.Close()
44
45 updConnCmd.Connection.Open()
46 updConnCmd.ExecuteNonQuery()
47 updConnCmd.Connection.Close()
48
49 End Sub
Anyone with an idea on how to solve this one? I think I need to write a subquery for the third condition, but I don't have a clue on how to handle this.Thanks in advance for your help!
View 2 Replies
View Related
Jun 16, 2004
Can someone give me a hand with this?
Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.
Todaysstock table updates the allstocks table with this statement,
insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks
this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?
ANy help would be appreciated. Thanks
View 4 Replies
View Related
May 28, 2002
Hello.
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
Thanks in advance for any replies.
David.
View 3 Replies
View Related
Feb 20, 2006
Hi @ all
I'm using MSSQL and PHP.
I've got the following sql statement:
$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')
The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused:
mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not.
I tried:
$result = mssql_query($msquery);
$succeed = mssql_rows_affected ($result);
And:
$result = mssql_query($msquery);
$succeed = mssql_num_rows($result);
to get the rows, affected by the statement, but both return:
supplied argument is not a valid MS SQL-Link resource
Anyone an idea?
View 2 Replies
View Related
Aug 23, 2004
I am having trouble finishing my query.
This is what I have:
IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})
I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.
Any suggestions would be appreciated.
Thanks,
Laura
View 13 Replies
View Related
Feb 26, 2008
When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.
I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.
My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.
I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.
View 3 Replies
View Related
Jul 20, 2005
In MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren
View 6 Replies
View Related
Jan 6, 2004
Im trying to select rows on the following criteria
My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.
I am able to return the following:
Comment UnReadMy Job 164
Comment UnReadFor Cam 166
Comment ReadThe Job 157
Comment ReadThird 159
Comment ReadMy Job 164
On Site Visit ReadMy newest job for log test 167
but as you can see jobid 164 appears in both groups and I need it to be one or the other
I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:
CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
@UserID INT
AS
BEGIN
IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead')
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead'
ORDER BY BackUp_Read ASC
END
ELSE
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'Read'
ORDER BY BackUp_Read ASC
END
END
GO
Thanks again
View 12 Replies
View Related
Nov 1, 2007
I'm trying to write a script that would only update a column if it exists.
This is what I tried first:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
UPDATE dbo.Enrollment SET nosuchfield='666'
END
And got the following error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'nosuchfield'.
I'm curious why MS-SQL would do syntax checking in this case. I've used this type of check with ALTER TABLE ADD COLUMN commands before and it worked perfectly fine.
The only way I can think of to get around this is with:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
declare @sql nvarchar(100)
SET @sql = N'UPDATE dbo.Enrollment SET nosuchfield=''666'''
execute sp_executesql @sql
END
which looks a bit awkward. Is there a better way to accomplish this?
View 3 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales
11 A 1000
23 A 1980
23 B 50
5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A
2)- if the store has type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 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
Jan 8, 2008
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
View 2 Replies
View Related
Nov 12, 2007
Greetings everyone,
I am trying to use a c# string with an SQL statement in a data adapter (.NET 03)
The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row:
WHERE (login = '" & test & "')
WHERE (login = '" + test + "')
any ideas?
PS: If I change to something like WHERE (login = 'abcdef') I get a result meaning there's something wrong with the way I am putting the variable in the sql query.
Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window
THANKS!
View 8 Replies
View Related
Dec 14, 2005
Hi,
I've created an sql statement:
select *
from fin_installment
where key_construction = (select ser_construction from fin_construction where key_contract = ' " & variable & " ') order by int_serial
which is in an Dataset's TableAdapter.
This variable receives its value during the form init and it is an integer.
When I start the page the folowing error message is displayed:
" An error has occurred during report processing.
Exception has been thrown by the target of an invocation.
Conversion failed when converting the varchar value ' " & azonosito & " ' to data type int. "
So my question is that how can I use variables in sql statement in dataset?
View 3 Replies
View Related
Dec 13, 2007
Hi, I am trying to use a variable inside a LIKE statement, but it is not working as expected. It will not give a error, but it shows no results while it does show results if I replace the variable with the normal string within the LIKE statement. Here is my code:
Code:
-- this example returns results
SELECT whatever
FROM mytable
WHERE whatever LIKE 'blah%';
Code:
-- this example returns no results
DECLARE @test VARCHAR;
SET @test='blah%';
SELECT whatever
FROM mytable
WHERE whatever LIKE @test;
Any ideas why the version using the variable would not work?
Patrick
View 3 Replies
View Related
Jun 17, 2004
I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this?
Here is the modified code:
/*
** Drop_Users_Company.sql
**
** This script will remove all users from the DYNGRP in the company database
** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
** It will then add all users back to the DYNGRP based on the SY60100 table.
** NOTE: You will need to replace %Companydb% with the company database
** name.
*/
/* Instead of replacing %Companydb% (in each USE statement) with the name of the
single company database that this script is supposed to work on, I've added
@cCompany to hold the company DB name through each iteration of the outside
cursor/while loop.
*/
declare @cCompany sysname/* ADDED BY ME FOR THE OUTSIDE LOOP */
declare @cStatement varchar(255)/* Misc exec string */
declare @DynDB varchar(15)/* DB Name exec string */
declare @DYNGRPgid int/* Id of DYNGRP group */
/*
** Loop through all company databases, emptying the DYNGRP group.
*/
SET QUOTED_IDENTIFIER OFF
use DYNAMICS
/* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */
declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS')
OPEN C_cursor
FETCH NEXT FROM C_cursor INTO @cCompany
WHILE (@@FETCH_STATUS <> -1)
begin
use @cCompany
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')
declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
use @cCompany
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end
/*
** Recreate the DYNGRP group in all company databases.
*/
use @cCompany
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end
end
DEALLOCATE C_cursor
______________________________________
Thanks for any help you have.
View 1 Replies
View Related
Aug 11, 2006
Declare @MyCode nvarchar(20);
Set @MyCode='ABC'
set @int_rowcount=(SELECT count(hoten) FROM @MyCode)
I run it but still errors !
How can i implement above statement ?
Thank you very much !
View 6 Replies
View Related
Jul 20, 2005
Hi,I am doing a really simple test with SQL Server 7.0:Using the Query AnalyzerLogged as saLocated in master database#1 USE Test#2 EXEC('USE Test')#1 => the database context is switched to Test#2 => the database is NOT switched???
View 2 Replies
View Related
Sep 3, 2007
Hello Everyone,
I wanted to pass a SQL statement thru a variable, and use that variable in my source component.
SELECT CLINIC_SUK, CLINIC_CODE, CLINIC_DESC, CLINIC_ARABIC, Load_DT
FROM DIM_CLINIC
where load_dt > ?
I had created a variable with my SQL statement and mapped that variable in my source component.
Its giving me some error.
Parameter Information cannot be derived from SQL statement. Set parameter information before preparing command.
Please do inform me about the solution for having a parameter in my source SQL Statement.
View 3 Replies
View Related
Apr 4, 2008
Hi ,
I am testing a very simple query that use variable for sort direction and sort expression
DECLARE
@SortExp nvarchar(256),
@SortDir nvarchar(10)
Set @SortExp = 'curTime'
Set @SortDir = 'DESC'
Select * from table
where recID < 20
order by @SortExp @SortDir
and i got this error...
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Is there anyway to do this task.
Thanks
Ddee
View 3 Replies
View Related
Dec 16, 2007
Hy, i have this problem in vb.net:
I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code:
Dim variable_name As StringDim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
When I don't use a variable after SET statement, everything work fine. This code works fine:
Dim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
Please, if someone can help me in this...thanks..
View 2 Replies
View Related
Apr 28, 2008
could anyone please help me to resolve this issue?
here's my sql query which retrieve last 3 month data
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) )
i need to pass a variable to retrieve data based on user requirements.
i tried this way,
dim mno as n
mno=4
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
its not working.
can i achieve this using stored procedure? or can i directly pass a variable to sql synatax?
thanks for any help
View 8 Replies
View Related
Oct 26, 2005
I'm curious if anyone knows the correct way to do this pseudo-statement correctly? I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName
View 4 Replies
View Related
Jan 2, 2001
I am having difficulties with some sql syntax with sql server 2000.
I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String.
Ex.
Declare @varA varchar(12)
select @varA = (select top 1 Value from #temp)
Update TableX
set @varA = y.ColTest
from TableX x, TableY y
where x.Colid = y.Colid
It sets the variable = to the last value from TableX.ColTest
I want the Update statement to update the value for the Variable which
represents the correct column to update.
Any ideas?
Thanks,
Daniel
View 1 Replies
View Related
Jan 18, 2002
Hi,
I want to use a variable in a 'use' statement...
but, I cannot figure out the syntax, nor do I know if it is possible...
Here is an example SQL script:
/*-----------------------------------------------------*/
DECLARE @DataBase varchar(60)
--Declare cursor for all DBs except master, MSDB, Model, tempdb
DECLARE curdb CURSOR for select name from master..sysdatabases
where
name not in ('master', 'MSDB', 'Model','tempdb')for read only
--Open and perform initial fetch
open curdb
fetch curdb into @DataBase
--While there are databases to process, process each DB
While @@fetch_status = 0
PRINT @DataBase
use + ' ' + @database --or,
use @database
fetch curdb into @DataBase
end
/*------------------------------*/
Thanks,
Michael
View 1 Replies
View Related
Jun 12, 2002
Hi
I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.
Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work.
I want to execute the SP withing each db.
Thanks
View 2 Replies
View Related
Mar 2, 2007
Hi all,
I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.
Code:
DECLARE @myval varchar(50),@username varchar(50)
DECLARE @rolelist varchar(2000)
SET @rolelist = ''
SET @myval = 'user a,user b'
select @myval = ''''+ replace(@myval,',',''',''') + ''''
print @myval
DECLARE User_Cursor CURSOR FOR
select distinct eusername
from euser
where eusername in (@myval)
OPEN User_Cursor
FETCH NEXT FROM User_Cursor INTO @username
SET @myval = @username
SET @rolelist = @username
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rolelist =+ @rolelist + ',' + @username
FETCH NEXT FROM User_Cursor INTO @username
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
print @myval
print 'rolelist' + @rolelist
GO
I am at a loss any suggestions would be greatly appreciated.
View 4 Replies
View Related
Dec 24, 2004
Hi All,
What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).
=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"
if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"
DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?
Thanks !
View 3 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
