Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dave1816
Starting Member
9 Posts |
Posted - 2012-12-21 : 05:05:45
|
Hi, I have some code which copies one user details over to another database which works well. However, now I want to replicate a user within the same DB, and just changing the username, password and e-mail addy.Therefore is it possible to amend the select statement below and pass the variables into the table? if not what the best way to approach this? as I’m receiving error when I try.[code]if object_id('tmp_copyuzr') is not null/* FILL IN THE USER INFORMATION WITHIN THE SINGLE QUOTES */SET @Username = '' --<--- TYPE IN the Username (what they use to log in to SL)SET @FROMdb = '' --<--- TYPE IN the name of the app db the User is inSET @TOdb = '' --<--- TYPE IN the name of the app db you want to move the User to-- Example: @FROMdb= 'OHIO_App' @TOdb = '[SRV001\INST4].TEXAS_app' -- Note: Only use the full linked server name if the TOdb is on a different SQL server /* Copy Username record */SET @SQL='INSERT INTO ' + @TOdb + '.dbo.usernames ( Username,EditLevel,SuperUserFlag,UserPassword,SQLServerLogin,SQLServerPassword,UserDesc,EmailAddress,WorkstationLogin,ConcurrentSessionsSpec)SELECT Username,EditLevel,SuperUserFlag,UserPassword,SQLServerLogin,SQLServerPassword,UserDesc,EmailAddress,WorkstationLogin,ConcurrentSessionsSpecFROM ' + @FROMdb + '.dbo.usernames t2 WHERE t2.username=''' + @username + ''''execute @Severity=sp_executesql @SQLif @Severity = 0 print '*** Username record inserted successfully'else begin print '### Error inserting username record.Rolling back transaction.' rollback tran return end [code]D.ARNOLD |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 05:11:45
|
you need to declare the variables before using inside sp_executesqlseehttp://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
dave1816
Starting Member
9 Posts |
Posted - 2012-12-21 : 05:52:17
|
quote: Originally posted by visakh16 you need to declare the variables before using inside sp_executesqlseehttp://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx
Hi visakh16,I have already done this at the top of my code (I havn't added it to this forum) My question really is if im using a select statement how do input a varibale for say the username? I get error 'Invalid Column Name if I put it into the statement.D.ARNOLD |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-21 : 06:16:02
|
In that link see 'Substituting Parameter Values' section to understand this concept..See this example..DECLARE @IntVariable INT;DECLARE @SQLString NVARCHAR(500);DECLARE @ParmDefinition NVARCHAR(500);/* Build the SQL string one time. */SET @SQLString = N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';/* Specify the parameter format one time. */SET @ParmDefinition = N'@SalesID int';/* Execute the string with the first parameter value. */SET @IntVariable = 275;EXECUTE sp_executesql @SQLString, @ParmDefinition, @SalesID = @IntVariable;/* Execute the same string with the second parameter value. */SET @IntVariable = 276;EXECUTE sp_executesql @SQLString, @ParmDefinition, @SalesID = @IntVariable;Here @SalesID is used in WHERE clause...While executing your prepared SQLString, you have to pass parameters definition as well as input values --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 06:37:49
|
quote: Originally posted by dave1816
quote: Originally posted by visakh16 you need to declare the variables before using inside sp_executesqlseehttp://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx
Hi visakh16,I have already done this at the top of my code (I havn't added it to this forum) My question really is if im using a select statement how do input a varibale for say the username? I get error 'Invalid Column Name if I put it into the statement.D.ARNOLD
Nope you've notI'm suggesting to include declaration of variables in sp_executesql call. you need to pass it as an argument as link shows------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|